Nth maximum salary

muraliksnv · New User Joined: 07 May 2005 Posts: 3

HI ALL,

1.WHAT IS THE QUERY FOR N th maximum salary in a table employee?please help me.

priya · Posted: Sun May 15, 2005 11:42 pm

Search the Forum. This Question is asked atleast once in a week.

i413678 · Posted: Mon May 16, 2005 5:18 pm

Hi,

This can be done in two ways using
1.subquery and
2.correlated subquery.

subquery:

1st highest salary : select max(sal) from emp;

2nd highest salary :

select max(sal) from emp where sal < ( select max(sal) from emp);

correlated subquery:

select sal from emp a where
n = ( select count(*) from emp b where a.sal<=b.sal);

replace the n value as 1 for first highest salary , 2 for 2nd highest salary, 3 for third highest salary and so on and so forth.

any doubts please let me know.

pavan