This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes SQL query help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL query help" Watch "SQL query help" New topic
Author

SQL query help

Pawanpreet Singh
Ranch Hand

Joined: Jun 12, 2005
Posts: 264

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

Joined: Oct 13, 2005
Posts: 37907
    
  22
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

Joined: Aug 13, 2004
Posts: 1081

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 ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL query help
 
Similar Threads
Query -- select maximum salary in a sum table
how to do this?
Simple Querry
Salary range in singapore for java/j2ee of 5.5+ experience
Expressing a One to Many Relationship in a Single Row