August 28, 2009 at 6:16 am
select * from EmpDetails
EmpIDEmpname ManagerId EmpWage
1001Lucky10110051000.00
1002Lucky10210052000.00
1003Lucky10310053000.00
1004Lucky10410054000.00
1005Lucky10510105000.00
1006Lucky10610106000.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
August 28, 2009 at 6:59 am
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
August 28, 2009 at 7:09 am
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
August 28, 2009 at 7:13 am
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
August 28, 2009 at 7:40 am
Correct. Instead of ORDER BY empid DESC you should use ORDER BY empwage DESC.
Thanks anyway.
-Lk
August 28, 2009 at 7:40 am
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?
August 28, 2009 at 7:50 am
Good suggestion Matt Thanks.
-LK
November 1, 2010 at 11:50 am
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
November 1, 2010 at 12:15 pm
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[/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 7, 2010 at 8:37 pm
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply