| sql-plz. help [message #8267] |
Wed, 06 August 2003 20:52  |
Deb
Messages: 17
Registered: November 2001
|
Junior Member |
|
|
An emp table contains empID.Emp name,Dept and salary.
To find the dept-wise max sal and the name of the emp who gets it.
Plz. reply quick on my email -id(iamdebasish@yahoo.com)
Thanks.
|
|
|
|
| Re: sql-plz. help [message #8270 is a reply to message #8267] |
Wed, 06 August 2003 21:15  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can use the RANK or DENSE_RANK analytic function to do this. I have used the Oracle emp demo table in the example below to show first how to use the dense_rank analytic function to obtain the dept-wise salary rank. Then, in the second query, I have used the first query as an inner sub-query and added an outer query to retrieve only those rows where the dept-wise salary rank is 1, which is what you asked for. You can find further information about the analytic functions in the Oracle on-line documentation.
SQL> SELECT ename, deptno, sal,
2 DENSE_RANK () OVER
3 (PARTITION BY deptno
4 ORDER BY sal DESC) AS dept_wise_sal_rank
5 FROM emp
6 /
ENAME DEPTNO SAL DEPT_WISE_SAL_RANK
---------- ---------- ---------- ------------------
KING 10 5000 1
CLARK 10 2450 2
MILLER 10 1300 3
SCOTT 20 3000 1
FORD 20 3000 1
JONES 20 2975 2
ADAMS 20 1155 3
SMITH 20 840 4
BLAKE 30 2850 1
ALLEN 30 1600 2
TURNER 30 1500 3
WARD 30 1250 4
MARTIN 30 1250 4
JAMES 30 950 5
14 rows selected.
SQL> SELECT ename, deptno, sal
2 FROM (SELECT ename, deptno, sal,
3 DENSE_RANK () OVER
4 (PARTITION BY deptno
5 ORDER BY sal DESC) AS dept_wise_sal_rank
6 FROM emp)
7 WHERE dept_wise_sal_rank = 1
8 /
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
FORD 20 3000
BLAKE 30 2850
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
