Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL query help  RSS feed

 
Pawanpreet Singh
Ranch Hand
Posts: 264
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have table with columns as name, deptno and salary.

I need to get the names which has maximum salary in their deptmenets.

I could able to find maximum salaries in each department using

select depno, max(sal) from emp GROUP BY deptno;

But how to get correspodning names?

Thanks
Pawan
SCJP 5.0, SCWCD 1.4
 
Campbell Ritchie
Sheriff
Posts: 53746
127
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Add "name" to the fields to be selected?

[edit]Add: That would probably work for a simple query, but you might need nested "selects" for anything more complicated.[/edit]
[ September 17, 2008: Message edited by: Campbell Ritchie ]
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Which Database are you using ?

you can try this query, I haven't tested it

select name, depno, sal from emp where (depno, sal) in (
select depno, max(sal) from emp GROUP BY deptno)


Shailesh
[ September 17, 2008: Message edited by: Shailesh Chandra ]
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!