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
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)
.
No comments:
Post a Comment