emp table [message #320298] |
Wed, 14 May 2008 11:11  |
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   |
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 #320306 is a reply to message #320305] |
Wed, 14 May 2008 11:36   |
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 #320382 is a reply to message #320299] |
Wed, 14 May 2008 22:34   |
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   |
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   |
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 #320734 is a reply to message #320644] |
Fri, 16 May 2008 02:20  |
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.
|
|
|