Take the max out of a column according to the max of another SQL server

Question

Take the max out of a column according to the max of another SQL server

Navigation

#1 by (2 votes)
#2 by (2 votes)
#3 by (0 votes)

0

Good I have a query where I have to make a select that gives me the maximum of one column depending on the maximum of another, I explain myself better with the following example

my table would be like this:

|nrocon|ordren|ordamp|
|  1   |  1   |  0   |
|  1   |  1   |  1   |
|  1   |  1   |  2   |
|  1   |  1   |  3   |
|  1   |  2   |  0   |
|  1   |  2   |  1   |
|  1   |  2   |  2   |
|  1   |  3   |  0   |
|  1   |  3   |  1   |
|  2   |  1   |  0   |
|  2   |  2   |  0   |
|  2   |  2   |  1   |
|  2   |  2   |  2   |
|  3   |  1   |  0   |
|  3   |  1   |  1   |

So, what I would need is that when doing the selection, give me the following information:

|nrocon|ordren|ordamp|
|  1   |  3   |  1   |
|  2   |  2   |  2   |
|  3   |  1   |  1   |

In other words, it would be the maximum ordamp of the maximum ordren of each node.

If anyone could give me a hand how I could do it, I'd appreciate it

Try the following:

select NroCon, max(ordren), max(ordamp) from Polizas
group by NroCon

But with this the table is like this:

|nrocon|ordren|ordamp|
|  1   |  3   |  3   |
|  2   |  2   |  2   |
|  3   |  1   |  1   |

in this example the difference would be in the nrocon 1, that the ordamp puts 3 instead of 1 which is the maximum of the ordren 3. I hope with this I understand better

sql sql-server

asked by Nicolas 11.04.2018 в 20:43

source

3 answers

2

I do not know if I understood your question well, but according to what I see it would be a Group by simple with two Max in the following way.

SELECT nrocon,MAX(ordren),MAX(ordamp) FROM [TuTabla]
GROUP BY nrocon

Please verify and comment if it is what you need. A happy day.

answered by 11.04.2018 в 21:01

0

select t1.nrocon,t1.ordren,max(t1.ordamp) as ordamp
from Tabla t1
where t1.ordren = (select max(t2.ordren) from Tabla t2 where t2.nrocon = t1.nrocon)
group by t1.nrocon,t1.ordren

answered by 11.04.2018 в 21:44

Know how many times a number is repeated in a Dataframe in R Because it does not sum my cycle for

score 2 · Accepted Answer

If I understood you correctly, one way to solve it is the following:

select  A.NroCon,
    A.max_ordren,
    B.max_ordamp
    from (select    NroCon, 
            max(ordren) max_ordren
            from Polizas
            group by NroCon
    ) A
    inner join (select  NroCon, 
                ordren, 
                max(ordamp) max_ordamp
                from Polizas
                group by NroCon,ordren
        ) B
        ON A.NroCon = B.NroCon
        AND A.max_ordren = B.ordren

In subquery B we obtain the maximum values of ordamp for each NroCon and ordren
Then, in A, we only need to obtain the maximum values but of ordren by NroCon
Then we simply do a JOIN of these two queries.