July 17, 2008 at 12:20 pm
I recommend the nested Order By solution, here's why:
;with CTE (Row, Number) as
(select row_number() over (order by col2), col2
from dbo.sometable)
select *
from cte
where row = 2
/*
=================
Without Index
=================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 4 ms.
(1 row(s) affected)
Table 'SomeTable'. Scan count 1, logical reads 51, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 2172 ms, elapsed time = 1682 ms.
=================
With Index
=================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(1 row(s) affected)
Table 'SomeTable'. Scan count 1, logical reads 3, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
*/
;with CTE as
(select top 2 col2
from dbo.sometable
order by col2)
select top 1 *
from CTE
order by col2 desc
/*
=================
Without Index
=================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.
(1 row(s) affected)
Table 'SomeTable'. Scan count 3, logical reads 16625, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 240 ms.
=================
With Index
=================
(1 row(s) affected)
Table 'SomeTable'. Scan count 1, logical reads 3, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
*/
If the column is indexed, both solutions are equally fast, but if the column isn't indexed (or isn't the root of an index), the nested order by solution is much faster. On the other hand, it does require slightly more IO, so your mileage may vary.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 17, 2008 at 1:14 pm
Let me enhance the Chris Morris solution a little. It does not give the right result in case there are ties (more than 1 persons with same salary).
Using the WITH TIES clause will solve this.
DROP TABLE #Employees
CREATE TABLE #Employees (Emp_No INT, EName VARCHAR(5), Sal INT)
INSERT INTO #Employees (Emp_No, EName, Sal)
SELECT 111, 'AAAA', 9999 UNION ALL
SELECT 222, 'bbbb', 9890 UNION ALL
SELECT 333, 'CCCC', 9890 UNION ALL
SELECT 777, 'vvvvv',7879
--SELECT TOP 1 * FROM (SELECT TOP 2 * FROM #Employees ORDER BY Sal) t ORDER BY Sal DESC
SELECT * FROM #Employees WHERE Sal in
(SELECT TOP 1 Sal from #Employees
WHERE Sal in (SELECT TOP 2 WITH TIES Sal FROM #Employees ORDER BY Sal)
ORDER BY Sal DESC)
The other solution can be written using the Rank() function of the SQL Server 2K5
The below forum topic addressed the same topic.
http://www.sqlservercentral.com/Forums/Topic472709-338-1.aspx#bm472904
Regards,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
July 17, 2008 at 1:23 pm
Please note that the alternative solution for this in SQL Server 2K5 should use the Dense_Rank() rather than Rank()
Regards,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
July 17, 2008 at 1:48 pm
Here is the solution using the Dense_rank(). The row_number() suggested by GSquared
will be ineffective in case of ties.
DROP TABLE #Employees
CREATE TABLE #Employees (Emp_No INT, EName VARCHAR(5), Sal INT)
INSERT INTO #Employees (Emp_No, EName, Sal)
SELECT 111, 'AAAA', 9999 UNION ALL
SELECT 222, 'bbbb', 9890 UNION ALL
SELECT 333, 'CCCC', 9890 UNION ALL
SELECT 777, 'vvvvv',7879
with CTE (row, Emp_No, EName, Sal) as
(select Dense_Rank() over (order by Sal desc) row, Emp_No, EName, Sal
from #Employees)
select Emp_No, EName, Sal
from cte
where row = 2
Regards,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
July 17, 2008 at 2:38 pm
Mazharuddin Ehsan (7/17/2008)
Here is the solution using the Dense_rank(). The row_number() suggested by GSquaredwill be ineffective in case of ties.
DROP TABLE #EmployeesCREATE TABLE #Employees (Emp_No INT, EName VARCHAR(5), Sal INT)
INSERT INTO #Employees (Emp_No, EName, Sal)
SELECT 111, 'AAAA', 9999 UNION ALL
SELECT 222, 'bbbb', 9890 UNION ALL
SELECT 333, 'CCCC', 9890 UNION ALL
SELECT 777, 'vvvvv',7879
with CTE (row, Emp_No, EName, Sal) as
(select Dense_Rank() over (order by Sal desc) row, Emp_No, EName, Sal
from #Employees)
select Emp_No, EName, Sal
from cte
where row = 2
Regards,
Maz
First, I didn't suggest Row_Number(), someone else did, and I suggested NOT using it.
Second, the original post was a request for the second row, not the "second highest/lowest whatever". If you need the second highest/lowest, then yes, Dense_Rank() is the best solution. If you want the second row, then nested Top...Order By is the best.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 17, 2008 at 3:18 pm
Thanks for your explanation GSquared and clarifying that Dense_rank() will work in this scenario not the row_number()
Yes the original poster is asking
how to select 2nd top row form EMP Table
As understood by me and all in this topic the criteria for the top is based on salary.
I hope we all can benefit from one of the above two solutions.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply