## Select second highest wage

 Author Message luckysql.kinda SSCertifiable Group: General Forum Members Points: 6110 Visits: 659 select * from EmpDetailsEmpID Empname ManagerId EmpWage1001 Lucky101 1005 1000.001002 Lucky102 1005 2000.001003 Lucky103 1005 3000.001004 Lucky104 1005 4000.001005 Lucky105 1010 5000.001006 Lucky106 1010 6000.00I need to select second last Empwage here.----------------SELECT TOP 1 EmpID, EmpWageFROM EmpdetailsWHERE Empwage <>(SELECT TOP 1 EmpWage FROM EmpDetailsORDER BY EmpwageDESC)ORDER BY EmpwageDESC-------------------This code works. But how to get fourth last Empwage ie Lucky103;3000? Is there any simple code here?-LK matt6288 SSCarpal Tunnel Group: General Forum Members Points: 4216 Visits: 1394 This is a simple way to start. You might have to adjust the logic to determine what row you want to be returned.`CREATE TABLE #temp (empid INT, empname VARCHAR(50), managerID INT, empWage MONEY)INSERT INTO #temp SELECT 1001, 'Lucky101', 1005, 1000.00UNION ALLSELECT 1002, 'Lucky102', 1005, 2000.00UNION ALLSELECT 1003, 'Lucky103', 1005, 3000.00UNION ALLSELECT 1004, 'Lucky104', 1005, 4000.00UNION ALLSELECT 1005, 'Lucky105', 1010, 5000.00UNION ALLSELECT 1006, 'Lucky106', 1010, 6000.00;WITH CTE AS (SELECT *, ROW_NUMBER() OVER (ORDER BY empWage desc) AS rowNumFROM #temp)SELECT * FROM cteWHERE rowNum = 4DROP TABLE #temp` luckysql.kinda SSCertifiable Group: General Forum Members Points: 6110 Visits: 659 Hey Matt Wilhoite,You could do this because you know the row number. Take a scenario where we have a lot of data and we don't know which row number we want.You need to simply give me the sixth highest Empwage. I think I am understood this time.-Lucky SQLGeek-652125 SSC-Enthusiastic Group: General Forum Members Points: 146 Visits: 10 Here is a very simple way to achieve what you are looking for.`SELECT TOP 1 empid,empWageFROM( SELECT TOP 4 empid,empWage FROM Empdetails ORDER BY empid DESC) a ORDER BY a.empid ASC` luckysql.kinda SSCertifiable Group: General Forum Members Points: 6110 Visits: 659 Correct. Instead of ORDER BY empid DESC you should use ORDER BY empwage DESC.Thanks anyway.-Lk matt6288 SSCarpal Tunnel Group: General Forum Members Points: 4216 Visits: 1394 The rowNumber is determined based off the row_number() function and it will be in the order of whatever is specified by the order by clause of that function. So yes, you asked for the 4th empWage so that is why the rowNum = 4. If you want the 6th then the rowNum would equal 6. Check out the row_number function in books online to see if it will do what you are needing.EDIT:Sorry just reread your earlier post. You say you won't know the wage you will want but then ask for the 6th highest. I am confused as to how you will implement this. You could use a variable in the rowNum = 4 line. Use something like rowNum = @variable. Would that work? luckysql.kinda SSCertifiable Group: General Forum Members Points: 6110 Visits: 659 Good suggestion Matt Thanks.-LK jerry-621596 SSCarpal Tunnel Group: General Forum Members Points: 4991 Visits: 649 I got it with this:`SELECT TOP 1 EmpID, MAX(EmpWage) AS [Max Wage]FROM EmpDetailsGROUP BY EmpIDHAVING MAX(EmpWage) < (SELECT TOP 1 MAX(EmpWage) FROM EmpDetails)ORDER BY [Max Wage] DESC` WayneS SSC Guru Group: General Forum Members Points: 78028 Visits: 10970 And what happens if the bottom 5 people have the worse wage, and what you want is the 6th person (2nd lowest wage)? You might want to use the RANK() function.`declare @TopWagePosition int;set @TopWagePosition = 2;WITH CTE AS(SELECT EmpID, EmpWage, RN = RANK() OVER (ORDER BY EmpWage DESC) FROM EmpDetails)SELECT * FROM CTE WHERE RN = @TopWagePosition;`You might want to see this article for how all of the Windowing Functions work: SQL Server Ranking Functions WayneMicrosoft Certified Master: SQL Server 2008Author - SQL Server T-SQL RecipesIf you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings Subbu S SSC Veteran Group: General Forum Members Points: 255 Visits: 138 Select * From #temp t1 Where (2-1) = (Select Count(Distinct(t2.empWage)) From #temp t2 Where t2.empWage > t1.empWage)The innery query will return a refernce to the current row. Genreal Format will be,Select * From #temp t1 Where (N-1) = (Select Count(Distinct(t2.empWage)) From #temp t2 Where t2.empWage > t1.empWage)The N should be replaced the by the number(Rank) which u hav to find.