How to select top 20 values record from database table

Question

I have a table called Employees, each employees has a salary value. I want to select the top 20 employees based on salary.

My database columns look like this:

EMPLOYEE SALARY DEPARTMENT 
--------------------------
KARTHI   500     IT
MANO     500     ADMIN
SANDY    300     HR
KARTHI   400     IT

I want to get top records like this:

EMPLOYEE SALARY DEPARTMENT 
---------------------------
KARTHI   900     IT
MANO     500     ADMIN
SANDY    300     HR

How to get top 5 records from database like above?

Please do some research before posting a question you can easily get already posted answers for same question. — Syed Wahhab, Nov 20 '19 at 05:42
Does this answer your question? [SQL - Select first 10 rows only?](https://stackoverflow.com/questions/1891789/sql-select-first-10-rows-only) — Jose Bagatelli, Nov 20 '19 at 06:05

Barbaros Özhan · Answer 1 · 2019-11-20T06:48:43.257

2

You can use row_number() window analytic function

select employee, salary, department 
  from
  (
   select e.*, row_number() over (order by e.salary desc) as rn
     from employees e
  ) e
 where rn <= 20;

If you have identical salaries(that's ties) for the boundary values such as the 20th-21st-22nd ranked people may have same salaries , then use :

select top 20 with ties employee, salary, department
  from employees 
 order by salary desc

to include all of the 22 people in the list. If you want to exclude 21st and 22nd people, then just use select top 20 * to ignore the ties.

edited Nov 20 '19 at 06:48

answered Nov 20 '19 at 05:38

Barbaros Özhan

59,113
10
31
55

1

`row_number` is kind of overkill for this. – Pரதீப் Nov 20 '19 at 05:45

score 1 · Answer 2 · answered Nov 20 '19 at 05:37

1

use top and order by salary desc

select top 20 EMPLOYEE, sum(SALARY) as salary, DEPARTMENT from 
tablename
group by employee,department
order by 2 desc

answered Nov 20 '19 at 05:37

Fahmi

37,315
5
22
31

but employee should one time but shows repeated value also – PriyaRiya Nov 20 '19 at 05:41
Do you really think companies will give more than one salary for an employee ? or part of two departments – Pரதீப் Nov 20 '19 at 05:45
its posted sample data – PriyaRiya Nov 20 '19 at 05:49
@Pரதீப், I don't think it's possible in real life but based on the op's data it seems to me that it happened :D – Fahmi Nov 20 '19 at 05:49
@PriyaRiya, could you please post the output which you are getting now? – Fahmi Nov 20 '19 at 05:50

score 0 · Answer 3 · answered Nov 20 '19 at 06:15

0

SELECT DISTINCT TOP 20 E.* FROM EMP AS E ORDER BY E.SALARY DESC

answered Nov 20 '19 at 06:15

Umair Rasheed

436
4
10

How to select top 20 values record from database table

3 Answers3