SQLServerCentral / SQL Server 2005 / Development / Select second highest wage / Latest Posts

RE: Select second highest wage

Subbu S — Sun, 07 Nov 2010 20:37:48 GMT

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.

RE: Select second highest wage

WayneS — Mon, 01 Nov 2010 12:15:04 GMT

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.[code="sql"]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;[/code]You might want to see this article for how all of the Windowing Functions work: [url=http://www.sqlservercentral.com/articles/T-SQL/69717/][u]SQL Server Ranking Functions[/u][/url]

RE: Select second highest wage

jerry-621596 — Mon, 01 Nov 2010 11:50:04 GMT

I got it with this:[code="sql"]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[/code]

RE: Select second highest wage

luckysql.kinda — Fri, 28 Aug 2009 07:50:35 GMT

Good suggestion Matt Thanks.-LK

RE: Select second highest wage

matt6288 — Fri, 28 Aug 2009 07:40:20 GMT

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?

RE: Select second highest wage

luckysql.kinda — Fri, 28 Aug 2009 07:40:19 GMT

Correct. Instead of ORDER BY empid DESC you should use ORDER BY empwage DESC.Thanks anyway.-Lk

RE: Select second highest wage

SQLGeek-652125 — Fri, 28 Aug 2009 07:13:08 GMT

Here is a very simple way to achieve what you are looking for.[code="sql"]SELECT TOP 1 empid,empWageFROM( SELECT TOP 4 empid,empWage FROM Empdetails ORDER BY empid DESC) a ORDER BY a.empid ASC[/code]

RE: Select second highest wage

luckysql.kinda — Fri, 28 Aug 2009 07:09:56 GMT

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

RE: Select second highest wage

matt6288 — Fri, 28 Aug 2009 06:59:21 GMT

This is a simple way to start. You might have to adjust the logic to determine what row you want to be returned.[code]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[/code]

Select second highest wage

luckysql.kinda — Fri, 28 Aug 2009 06:16:07 GMT

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