Home » SQL & PL/SQL » SQL & PL/SQL » emp table
emp table [message #320298] Wed, 14 May 2008 11:11 Go to next message
mrpranab
Messages: 32
Registered: March 2005
Member
Hi All,

i need the highest salary payment details in each department from emp table. In the result i need all the fields of emp table...
e.g. if there are 3 deptno in emp table then 3 rows should fetch with the highest salary one employee details...

can i have the sql statements for this type of query...

thanks...
Re: emp table [message #320299 is a reply to message #320298] Wed, 14 May 2008 11:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What should happen if two enployees in the same department both get the maximum salary
Re: emp table [message #320305 is a reply to message #320298] Wed, 14 May 2008 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To help you understand JRowbottom's question, have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Re: emp table [message #320306 is a reply to message #320305] Wed, 14 May 2008 11:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Shhhh! You're spoiling the fun.

I want to see what happens when the OP realises that his homework question requires additional information to answer
Re: emp table [message #320313 is a reply to message #320306] Wed, 14 May 2008 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ooops! sorry, I'm really slow this last couple of days.

Regards
Michel
Re: emp table [message #320314 is a reply to message #320313] Wed, 14 May 2008 11:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Smile
Re: emp table [message #320382 is a reply to message #320299] Wed, 14 May 2008 22:34 Go to previous messageGo to next message
mrpranab
Messages: 32
Registered: March 2005
Member
JRowbottom - If 2 employees get same highest salary in the same dept then both should retrieve.

Michel - i will go through the functions.

Thanks.
Re: emp table [message #320438 is a reply to message #320382] Thu, 15 May 2008 02:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In which case, a good solution would involve using the RANK or DENSE_RANK functions on the salary, partitioning by Dept_no to get a list of the highest salaries per department, and then picking only those rows with a RANK of 1
Re: emp table [message #320644 is a reply to message #320438] Thu, 15 May 2008 14:06 Go to previous messageGo to next message
mrpranab
Messages: 32
Registered: March 2005
Member
can we use DENSE_RANK function as rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank.
Re: emp table [message #320722 is a reply to message #320644] Fri, 16 May 2008 01:37 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Yes
Re: emp table [message #320734 is a reply to message #320644] Fri, 16 May 2008 02:20 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As the only rows you're interested in for this problem are the highest salary per department, both RANK and DENSE RANK will give the same results.
Previous Topic: SQL join question
Next Topic: ORA-01427: single row subquery returns more than one row
Goto Forum:
  


Current Time: Thu Dec 08 16:24:23 CST 2016

Total time taken to generate the page: 0.10074 seconds