15,499,155 members
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.

Posted
Updated 29-Sep-21 3:21am

## Solution 2

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
Prosan 31-May-12 7:27am
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

## Solution 3

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
Member 11781195 30-Jun-15 21:50pm
this query is not perfect... question how to get top 5 salary in employee table

## Solution 7

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

## Solution 1

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
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.

## Solution 5

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

## Solution 10

SQL
```SELECT TOP 5 salary
FROM employee
ORDER BY salary DESC```

v2

## Solution 6

SELECT * FROM tablename WHERE Salary in(SELECT TOp 5 Salary from tablename GROUP BY Salary ORDER BY DESC)

## Solution 8

How to get top 5 salary data in sql query[^]

## Solution 13

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
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
:)

## Solution 33

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;