Click here to Skip to main content
15,077,701 members
Please Sign up or sign in to vote.
1.50/5 (6 votes)
See more:
Suppose i have a table with salary column

now i want a single query which returns all the employees who have top 5 salaries.

If 33 employees have 30000 salary then all should come.

Thanks in advance
Posted
Updated 29-Sep-21 2:21am

This should work. I have tested it on my machine

SQL
SELECT *
FROM table
WHERE 
(
  sal IN 
  (
    SELECT TOP (5) sal
    FROM table as table1
    GROUP BY sal
    ORDER BY sal DESC
  )
)


sal = salary column
table = table name
   
v2
Comments
Prosan 31-May-12 7:27am
   
good answer
rakesh@bbspl.com 31-May-12 7:34am
   
Hi Rahul Thanks, but i don't wish to use IN statment. This will make the query execution time longer and i am working on a big one stored procedure. I want to use some like "WITH TIES".

Please Suggest
Member 11749499 8-Jun-15 5:25am
   
This code is not working please review
asish312 30-Aug-16 11:03am
   
Yes.This code is not working and it's returning the same table
Hi It will definatly work:
SQL
select  * from employee where salary in (select distinct top 5 salary from employee order by salary desc)


SELECT TOP 5 WITH TIES Name, Salary FROM tlevel ORDER BY Salary desc
   
v3
Comments
Member 11781195 30-Jun-15 21:50pm
   
this query is not perfect...

question

how to get top 5 salary in employee table
SQL
with Result as
(
   select dense_rank () over (order by salary) as 'TopSalary',* from table name
) select * from Result where TopSalary between 1 and 5
   
v2
hi
SQL
SELECT DISTINCT TOP 5 salary
FROM employee
ORDER BY salary DESC


solution 2 is correct write like solution 2

as per Solution 2
SQL
SELECT *
FROM table
WHERE
(
 salary IN
 (
  SELECT TOP (5) salary
  FROM table as table1
  GROUP BY sal
  ORDER BY sal DESC
 )
)
   
v2
Comments
Rahul Rajat Singh 31-May-12 7:22am
   
this will only give top 5 salary figures, he need all the records with these figures.
sunandandutt 31-May-12 7:29am
   
Friends My code will Defiantly work Kindly Check.
Try this:
Make Inner Join on your subquery with desired salary span.
SQL
SELECT * FROM yourTable AS T1
INNER JOIN 
(SELECT DISTINCT TOP 5 Salary FROM yourTable ORDER BY Salary DESC) AS T2
ON T1.Salary = T2.Salary 


ORDER BY Salary DESC
   
SQL
SELECT TOP 5 salary
FROM employee
ORDER BY salary DESC
   
v2
SELECT * FROM tablename WHERE Salary in(SELECT TOp 5 Salary from tablename GROUP BY Salary ORDER BY DESC)
   
Please follow the link.. try to search in google if you dont get your answer then post in CP

How to get top 5 salary data in sql query[^]
   
Say Employe table contains emp_id, salary, rank, dept_id as columns and has some data in it.

1. To get max salary from Employee table.
SQL
SELECT MAX(salary) FROM employee;


2. To get max salary from Employee table with respect to dept.
SQL
SELECT MAX(slary), dept_id from employee group by dept_id;


3. To get top 5 salary
SQL
select distinct salary from employee order by salary desc limit 5;

4. To get top 5 salary with respect to dept
SQL
select distinct salary, dept_id from employee order by salary desc limit 5;


5. To get 2nd max salary
SQL
select max(salary) from employee where salary not in(select max(salary) from employee);


5. To get 2nd max salary with respect to department
SQL
select max(salary), dept_id from employee where salary not in(select max(salary) from employee) group by dept_id;


Hope, this will give better idea on queries to get max number and with respect to other field.

Suppose if you have rank in column and would like you to find rank, then make sure use MIN in place of MAX.

Cheers....
   
v2
Comments
Nirav Prabtani 29-May-14 1:52am
   
limit????
Are you sure??
CHill60 29-May-14 6:42am
   
I hope not ;-)
CHill60 29-May-14 6:40am
   
And two years on it's Solution number 13!
Nirav Prabtani 29-May-14 6:57am
   
:)
select * from employees e1
where (select count (distinct salary) from employees e2
where e2.salary>= e1.salary) between 1 and 5 order by salary desc;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900