to find 4th maximum in a sql table

Question

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

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

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 1

try looking into RANK() or ROW_NUMBER() functions.....

Kev

steveb. SSC-Forever Points: 46733 More actions · Answer 2

If you post your table def, and some sample data i am sure there is a solution...

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

You really need to read this article:

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 4

Hii,

There are different ways to get this..!

Try This...

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

DECLARE @Emp Table ( ID int, Name Varchar(10), Salary int)

INSERT INTO @Emp

SELECT 1,'A',20000 UNION

SELECT 2,'B',25000 UNION

SELECT 3,'C',15000 UNION

SELECT 4,'D',18000 UNION

SELECT 5,'E',22000 UNION

SELECT 6,'F',24000

-- First Method

SELECT Min(Salary) FourthMax

FROM (SELECT TOP 4 * FROM @Emp ORDER BY Salary Desc) AS Temp

-- Second Method

SELECT Name,Salary

FROM (SELECT *,Row_Number() OVER (Order By Salary Desc) AS Row_Number FROM @Emp) as Temp

WHERE Row_Number = 4

If still you have some problem then feel free to ask... 😛

Cheers,
Hari
Tips & Tricks for SQL BI Developers