MySQL/PostgreSQL - nth max salary

Setup:

create table employee

(

id serial,

name varchar(150),

salary float

);

insert into employee(name,salary) values ('sagar','100000'),

('sangram','50000'),

('sachin','56000'),

('swapnil','45000'),

('gaurav',54000),

('ganesh',39000);

insert into employee(name,salary) values ('gajanan',54000),

('kamal',39000);

Nth Max Salary

select * from employee where salary = (

select salary from employee order by salary desc limit 1 offset n-1

)

3rd Max salary:

select * from employee where salary = (

select salary from employee order by salary desc limit 1 offset 2

)

Nth Max Salary Using Inner Join:

select s.* from employee s

inner join (

select distinct salary from employee order by salary desc limit 1 offset n-1

) t on s.salary = t.salary

3rd Max salary:

select s.* from employee s

inner join (

select distinct salary from employee order by salary desc limit 1 offset 2

) t on s.salary = t.salary

Nth Max Salary generic:

select * from employee e1 where n-1=(select count(distinct salary) from employee e2 where e2.salary > e1.salary)

3rd Max Salary:

select * from employee e1 where 2=(select count(distinct salary) from employee e2 where e2.salary > e1.salary)

Nth Max salary using DENSE_RANK():

select

T1.name,

T1.salary

from (

SELECT

DENSE_RANK() OVER (ORDER BY SALARY DESC) AS rownumber,

Salary,

name

FROM

employee

)T1 where T1.rownumber = n

3rd Max salary:

select

T1.name,

T1.salary

from (

SELECT

DENSE_RANK() OVER (ORDER BY SALARY DESC) AS rownumber,

Salary,

name

FROM

employee

)T1 where T1.rownumber = 3

Nth Max salary using ROW_NUMBER():

SELECT * FROM

(

SELECT

ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS rownumber,Salary

FROM (select distinct salary from employee)T1

) AS foo

inner join Employee E2 on E2.salary = foo.salary

WHERE

rownumber = n

3rd Max salary:

SELECT * FROM

(

SELECT

ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS rownumber,Salary

FROM (select distinct salary from employee)T1

) AS foo

inner join employee E2 on E2.salary = foo.salary

WHERE

rownumber = 3