SQL to fetch least 2 salaries from a table department wise

Question

How would be the SQL to fetch least 2 salaries from a table department wise ?

Sample Table:

empid  salary   Dept
---------------------
101     2000    aaa
102     1000    bbb
103     5000    bbb
104     8000    ccc
105     3000    aaa
106     4000    aaa
107     6000    ccc
108     7000    bbb
109     9000    ccc

Output should be like:

Dept  empid   salary
----------------------
aaa    101     2000
aaa    105     3000
bbb    102     1000
bbb    103     5000
ccc    104     6000
ccc    107     8000

Does this answer your question? [In SQL, how to select the top 2 rows for each group](https://stackoverflow.com/questions/15969614/in-sql-how-to-select-the-top-2-rows-for-each-group) — Andrew, Nov 14 '19 at 20:41

score 0 · Answer 1 · answered Nov 14 '19 at 19:47

SELECT
    t.dept
    ,t.empid
    ,t.salary
FROM
    #test t
WHERE
    t.empid IN (
        SELECT TOP 2
            empid
        FROM
            #test
        WHERE
            dept = t.dept
        ORDER BY
            salary
    )
ORDER BY
    dept,empid

As was pointed out, there may be other people in dept ccc with a salary of 8000 that will be missed.

score 0 · Answer 2 · answered Nov 14 '19 at 20:50

Following this blog post and supposing the table name is "samptab":

select dept, empid, salary
from samptab
where (
   select count(*) from samptab as s
   where s.dept = samptab.dept and s.salary <= samptab.salary
) <= 2 order by dept;

You can change the number "2" in the query row if you want more or less rows.

SQL to fetch least 2 salaries from a table department wise

2 Answers2