0

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.

Sreedhar Danturthi
  • 7,119
  • 19
  • 68
  • 111

1 Answers1

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).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Could you please add some explanation for the query you have written. Thank you in anticipation – Sreedhar Danturthi Jul 22 '17 at 11:01
  • @dsfasdfadf . . . Is there a reason you unaccepted the answer? – Gordon Linoff Jul 29 '17 at 18:46
  • I wanted to delete the question since this question is duplicate of this question: Select first row in each GROUP BY group? as pointed out by other users already. What I thought was if I unaccept the answer StackOverflow would allow me to delete but it would not since there is an answer to my question. Thanks for your great help. Your solution accepts perfectly well. – Sreedhar Danturthi Aug 01 '17 at 14:26
  • I would add to Gordon's answer that in MySQL < 5.6, you better use a LEFT JOIN clause instead of using a subquery with "select max". Constant subquery results are not cached by the database, therefore the WHERE clause will be fully evaluated for every row the WHERE clause will examine, which can significantly impact query performance. You can use [EverSQL SQL Query Optimizer](http://www.eversql.com) to optimize the query automatically (disclaimer: I'm a co-founder at EverSQL and I humbly suggest the usage of our product). – Tomer Shay Aug 03 '17 at 06:24
  • @TomerSH . . . Even prior to 5.6, MySQL would use an index for the correlated subquery. – Gordon Linoff Aug 03 '17 at 11:57