Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Select second highest wage Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, August 28, 2009 6:16 AM
 Old Hand Group: General Forum Members Last Login: Thursday, May 23, 2013 10:41 PM Points: 310, Visits: 649
 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
Post #779010
 Posted Friday, August 28, 2009 6:59 AM
 Ten Centuries Group: General Forum Members Last Login: Friday, December 13, 2013 9:05 AM Points: 1,207, Visits: 1,234
 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`
Post #779041
 Posted Friday, August 28, 2009 7:09 AM
 Old Hand Group: General Forum Members Last Login: Thursday, May 23, 2013 10:41 PM Points: 310, Visits: 649
 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
Post #779054
 Posted Friday, August 28, 2009 7:13 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, September 21, 2009 4:48 PM Points: 4, 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`
Post #779056
 Posted Friday, August 28, 2009 7:40 AM
 Old Hand Group: General Forum Members Last Login: Thursday, May 23, 2013 10:41 PM Points: 310, Visits: 649
 Correct. Instead of ORDER BY empid DESC you should use ORDER BY empwage DESC.Thanks anyway.-Lk
Post #779089
 Posted Friday, August 28, 2009 7:40 AM
 Ten Centuries Group: General Forum Members Last Login: Friday, December 13, 2013 9:05 AM Points: 1,207, Visits: 1,234
 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?
Post #779090
 Posted Friday, August 28, 2009 7:50 AM
 Old Hand Group: General Forum Members Last Login: Thursday, May 23, 2013 10:41 PM Points: 310, Visits: 649
 Good suggestion Matt Thanks.-LK
Post #779103
 Posted Monday, November 01, 2010 11:50 AM
 Old Hand Group: General Forum Members Last Login: Tuesday, September 03, 2013 4:05 PM Points: 367, Visits: 608
 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`
Post #1014003
 Posted Monday, November 01, 2010 12:15 PM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 9:26 AM Points: 6,466, Visits: 8,550
 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 2008If 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
Post #1014023
 Posted Sunday, November 07, 2010 8:37 PM
 SSC Rookie Group: General Forum Members Last Login: Monday, December 17, 2012 1:49 AM Points: 45, 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.
Post #1016994

 Permissions