How do I compare row values in SQL Query result?

Question

I have got SQL sub-query result as follows

A B C
1 2 12
1 4 10
1 3 11
2 5 9
2 3 8
4 7 8
4 9 3

Now I have to output values in such a manner for each distinct value in Column A and and for the highest value in column B, I have to return the corresponding value in column C

The output will be

A B C
1 4 10
2 5 9
4 9 3

How can I accomplish this.

Answer 1

You can use the ANSI standard function row_number():

select a, b, c
from (select t.*,
             row_number() over (partition by a order by b desc) as seqnum
      from t
     ) t
where seqnum = 1;

The row_number() function assigns a sequential value to each row. In this case, all rows with the same value of a are assigned the same value, with "1" for the largest b value. You want the first, hence the seqnum = 1.

MySQL does not support this function. Instead, just do:

select t.*
from t
where t.b = (select max(t2.b) from t t2 where t2.a = t.a);

If you care about performance, you want an index on t(a, b).