Search Flipkart

Friday, February 7, 2014

get the rows from table for maximum value of a column

select e.*
from table e
inner join (select column1,MAX(column2) v from table where column2='xxxxxx' and column4='yyyyy' group by column1) m
  on e.column1 = m.column1 and e.column2 = m. v ;

This can be useful for extracting data from table like
Name Salary Dept
User1 5000         IT
User2 10000 IT
User3 12000 Civil
User4 25500 Aviation
User5 16000 Civil

And the should be rows containing  highest salary in the Dept
User2 10000 IT
User5 16000 Civil
User4 25500 Aviation

No comments:

Post a Comment