Click here to Skip to main content
15,303,151 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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
Comments
Hiren solanki 4-Oct-10 6:26am
   
added 'pre' tags for code visibility.

SQL
SELECT * FROM
(
SELECT RANK() OVER (PARTITION BY DEPT ORDER BY SAL DESC) AS 'Rank', *
FROM EMP
) AS A WHERE RANK < =2
   
Comments
Kapil Dhokai 22-Mar-12 11:45am
   
Thanks for short and perfect Answer.
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


Please vote and Accept Answer if it Helped.
   
SELECT * FROM #emp WHERE Dept=@DEPT LIMIT 0, 2 ORDER BY Sal ASC

Then use
AddWithValue("@DEPT", departmentText);
   
select * from(select row_number() over(partition by dep order by salary desc)rownum,* from emp)dep_db where dep_db.rownum<=2
   
Comments
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.

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