Home » SQL & PL/SQL » SQL & PL/SQL » to get highest sal dept-wise
to get highest sal dept-wise [message #199328] Mon, 23 October 2006 10:12 Go to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello all
i m using query to get department wise highest sal from emp table, thouugh my query run well there is no null value but
fails when there is null value.
please make my query correct.

this is my query

SQL>select * from (select client_no, name, sal, dept_no, emp, job, dense_rank () over(partition by dept_no order by sal desc)rank from emp1) where rank=1;

CLIENT NAME SAL DEPT_NO EMP JOB RANK
------ ---------------- ---------- ---------- ---------- -------- ----------
C00004 Basu Navindgi 400000 10 500 manager 1
C00013 Ritika 376532 20 390 supreme 1
C00002 Vandana Saitwal 30 200 manager 1
C00006 Rukmini 200000 40 500 manager 1



SQL>select * from (select client_no, name, sal, dept_no, emp, job, dense_rank () over(partition by dept_no order by sal desc)rank from emp1) where rank=1 and sal is not null;


CLIENT NAME SAL DEPT_NO EMP JOB RANK
------ ---------------- ---------- ---------- ---------- -------- ----------
C00004 Basu Navindgi 400000 10 500 manager 1
C00013 Ritika 376532 20 390 supreme 1
C00006 Rukmini 200000 40 500 manager 1

SQL>select * from (select client_no, name, sal, dept_no, emp, job, dense_rank () over(partition by dept_no order by sal desc)rank from emp1) where sal is not null and rank=1 ;


CLIENT NAME SAL DEPT_NO EMP JOB RANK
------ ---------------- ---------- ---------- ---------- -------- ----------
C00004 Basu Navindgi 400000 10 500 manager 1
C00013 Ritika 376532 20 390 supreme 1
C00006 Rukmini 200000 40 500 manager 1


if there is not null then its run well show's 600000 on dept_no. 30 but show's nothing when there is null.

thanx
Re: to get highest sal dept-wise [message #199335 is a reply to message #199328] Mon, 23 October 2006 11:45 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You should specify NULLS LAST:

select * from (
	select client_no, name, sal, dept_no, emp, job,
	dense_rank () over(partition by dept_no order by sal desc nulls last) rank
from emp1) where rank=1;

Re: to get highest sal dept-wise [message #199580 is a reply to message #199328] Wed, 25 October 2006 03:06 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> select * from highsal;

    DEPTNO        SAL
---------- ----------
        10        100
        10        200
        10        300
        20        110
        20        120
        20        130
        30        125
        30        115
        30        105

9 rows selected.


SQL> select deptno,max(sal) from highsal group by deptno;

    DEPTNO   MAX(SAL)
---------- ----------
        10        300
        20        130
        30        125
Re: to get highest sal dept-wise [message #199604 is a reply to message #199580] Wed, 25 October 2006 04:10 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

The following query gives the details of employees whose salary is maximum in their respective departments.


select * from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno)
Re: to get highest sal dept-wise [message #199700 is a reply to message #199604] Wed, 25 October 2006 21:34 Go to previous message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
thanx to u all .

what i need from my question, got from ebrian's solution.
it works properly.

thanx & regards
ashish

[Updated on: Wed, 25 October 2006 21:35]

Report message to a moderator

Previous Topic: SQL Look Back
Next Topic: SQL Update Statement help
Goto Forum:
  


Current Time: Sat Dec 03 14:17:33 CST 2016

Total time taken to generate the page: 0.15325 seconds