Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select second highest wage Expand / Collapse
Author
Message
Posted Friday, August 28, 2009 6:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, Visits: 659
select * from EmpDetails

EmpID Empname ManagerId EmpWage
1001 Lucky101 1005 1000.00
1002 Lucky102 1005 2000.00
1003 Lucky103 1005 3000.00
1004 Lucky104 1005 4000.00
1005 Lucky105 1010 5000.00
1006 Lucky106 1010 6000.00


I need to select second last Empwage here.

----------------
SELECT TOP 1 EmpID, EmpWage
FROM Empdetails
WHERE Empwage <>
(SELECT TOP 1 EmpWage FROM EmpDetails
ORDER BY Empwage
DESC
)
ORDER BY Empwage
DESC
-------------------
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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:22 PM
Points: 1,207, Visits: 1,273
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.00
UNION ALL
SELECT 1002, 'Lucky102', 1005, 2000.00
UNION ALL
SELECT 1003, 'Lucky103', 1005, 3000.00
UNION ALL
SELECT 1004, 'Lucky104', 1005, 4000.00
UNION ALL
SELECT 1005, 'Lucky105', 1010, 5000.00
UNION ALL
SELECT 1006, 'Lucky106', 1010, 6000.00



;WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY empWage desc) AS rowNum
FROM #temp
)

SELECT * FROM cte
WHERE rowNum = 4

DROP TABLE #temp

Post #779041
Posted Friday, August 28, 2009 7:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, 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
Post #779054
Posted Friday, August 28, 2009 7:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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,empWage
FROM
(
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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, Visits: 659
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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:22 PM
Points: 1,207, Visits: 1,273
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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, Visits: 659
Good suggestion Matt Thanks.

-LK
Post #779103
Posted Monday, November 1, 2010 11:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:58 PM
Points: 367, Visits: 615
I got it with this:

SELECT TOP 1 EmpID, MAX(EmpWage) AS [Max Wage]
FROM EmpDetails
GROUP BY EmpID
HAVING MAX(EmpWage) < (SELECT TOP 1 MAX(EmpWage) FROM EmpDetails)
ORDER BY [Max Wage] DESC

Post #1014003
Posted Monday, November 1, 2010 12:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 5,364, Visits: 8,951
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


Wayne
Microsoft Certified Master: SQL Server 2008
If 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 7, 2010 8:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse