Display salary rank

Question

Post reply

Display salary rank

Murphy'sLaw

Right there with Babe

Points: 725
More actions
February 4, 2012 at 3:38 pm

#252313

-- Dispaly 5th highest and 2nd lowest salary in the same table (without rank functions)
select * from (
select top 1 empName,Salary from SalaryTable where salary in
(
select top(5) Salary from SalaryTable order by Salary desc
)
order by Salary
)p
union all
select * from (select top 1 empname,salary from SalaryTable where salary in
(
select top 2 Salary from SalaryTable order by salary
)
order by salary desc
)q
Allthough this served the purpose.I am not happy. Any suggestions for better solution.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 1

What's not to be happy with there? It could be shortened a bit but would still be basicly the same thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

Murphy'sLaw (2/4/2012)
-- Dispaly 5th highest and 2nd lowest salary in the same table (without rank functions)

Why "without rank functions"...??? (homework maybe 😉 )

_______________________________________________________________________

Not sure what you are expecting if you have duplicate results....take for instance

USE [tempdb]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SalaryTable]') AND type in (N'U'))

DROP TABLE [dbo].[SalaryTable]

GO

CREATE TABLE [dbo].[SalaryTable](

[EmpName] [varchar](3) NULL,

[Salary] [int] NULL

)

GO

INSERT INTO [dbo].[SalaryTable]([EmpName], [Salary])

SELECT N'A', 55500 UNION ALL

SELECT N'B', 42500 UNION ALL

SELECT N'C', 23000 UNION ALL

SELECT N'D', 54000 UNION ALL

SELECT N'E', 27000 UNION ALL

SELECT N'F', 31000 UNION ALL

SELECT N'G', 17000 UNION ALL

SELECT N'H', 18000 UNION ALL --- duplicate 2nd lowest

SELECT N'I', 27500 UNION ALL

SELECT N'J', 29000 UNION ALL

SELECT N'K', 18000 --- duplicate 2nd lowest

note that two employees have 2nd lowest salary

your code returns

empName Salary

------- -----------

J 29000

H 18000

personally ... I would expect to see

EmpName Salary

------- -----------

J 29000

H 18000

K 18000

but, of course your requirements maybe different.

kind regards

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

Assuming that by Ranking Functions you mean RANK,DENSE_RANK,NTILE & ROW_NUMBER are to be excluded

http://msdn.microsoft.com/en-us/library/ms189798.aspx

here is one possible way that manages duplicates

SELECT Salary,rn = IDENTITY(INT, 1, 1)

INTO #tmp

FROM SalaryTable

GROUP BY Salary

ORDER BY Salary DESC

SELECT S.EmpName, #tmp.Salary

FROM #tmp INNER JOIN SalaryTable S ON #tmp.Salary = S.Salary

WHERE (#tmp.rn = 5) OR (#tmp.rn =(SELECT MAX(rn) - 1 FROM #tmp ))

ORDER BY #tmp.Salary DESC

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

johnitech.itech Hall of Fame Points: 3252 More actions · Answer 4

or can try below code for 2nd lowest salary

select * from emp where sal in (select * from (select min(sal) from emp group by sal order by sal ) where rownum <=2)