15,303,151 members
1.00/5 (1 vote)
See more:
```CREATE TABLE #emp
(
Dept varchar(100),
Emp varchar(100),
Sal numeric(18,2)
)
INSERT INTO #Emp values('Comp','A',6000)
INSERT INTO #Emp values('Comp','B',7000)
INSERT INTO #Emp values('Comp','C',10000)
INSERT INTO #Emp values('Comp','D',9000)
INSERT INTO #Emp values('Elec','P',2000)
INSERT INTO #Emp values('Elec','Q',10000)
INSERT INTO #Emp values('Elec','R',11000)
INSERT INTO #Emp values('Eng','AA',15000)
INSERT INTO #Emp values('Eng','BB',2000)```

/* I want the output (Department wise top 2 highest salary, where department might be dynamic text)
Dept Emp Sal
----------------------------
Comp C 10000
Comp D 9000
Elec R 11000
Elec Q 10000
Eng AA 15000
Eng BB 2000
*/
Posted
Updated 5-Sep-21 23:35pm
v2
Hiren solanki 4-Oct-10 6:26am

added 'pre' tags for code visibility.

## Solution 3

SQL
```SELECT * FROM
(
SELECT RANK() OVER (PARTITION BY DEPT ORDER BY SAL DESC) AS 'Rank', *
FROM EMP
) AS A WHERE RANK < =2```
Kapil Dhokai 22-Mar-12 11:45am

Thanks for short and perfect Answer.

## Solution 4

SQL
```WITH Temp_Table
AS
(
select ROW_NUMBER() over (Partition by dept order by sal) rownum,* from #emp
)
select * from Temp_Table where rownum < 3```

## Solution 2

`SELECT * FROM #emp WHERE Dept=@DEPT LIMIT 0, 2 ORDER BY Sal ASC`

Then use
`AddWithValue("@DEPT", departmentText);`

## Solution 5

`select * from(select row_number() over(partition by dep order by salary desc)rownum,* from emp)dep_db where dep_db.rownum<=2`
Richard Deeming 6-Sep-21 5:39am

Essentially identical to solutions 3 and 4, which were posted eleven years ago!

If you're going to dig up an ancient question, make sure you have read the existing solutions, and that you are adding something new to the discussion. Otherwise, stick to answering new questions.