June 10, 2008 at 4:48 am
Hi,
Good afternoon
how to select 2nd top row form EMP Table,
eg:-
Emp No EName Sal
111 AAAA 9999
222 bbbb 9890
777 vvvvv 7879
plz help me to achive this...
Regds,
rclgoriparthi.
June 10, 2008 at 5:06 am
try this.
select * from #temp where empno in (select top 1 empno from #temp where empno > (select top 1 empno from #temp))
Assume #temp is table instead of EMP.
thanks,
vijay
June 10, 2008 at 5:28 am
The code below will also work, change the ORDER BY according to the column that you want the second row from, I assumed it was the Sal column.
WITH Empl AS
(
SELECT
ROW_NUMBER() over(ORDER BY Sal DESC) AS RowNumber,
EmpNo,
EName,
Sal
FROM
Employee
)
SELECT
*
FROM
Empl
WHERE
RowNumber = 2
Hope this helps.
MB
June 11, 2008 at 1:31 am
Mark Beringer (6/10/2008)
The code below will also work, change the ORDER BY according to the column that you want the second row from, I assumed it was the Sal column.WITH Empl AS
(
SELECT
ROW_NUMBER() over(ORDER BY Sal DESC) AS RowNumber,
EmpNo,
EName,
Sal
FROM
Employee
)
SELECT
*
FROM
Empl
WHERE
RowNumber = 2
Hope this helps.
MB
Well, well, well I am amazed. Very good answer there MB. I came here to see if I could help and I learned something new today.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
June 11, 2008 at 1:43 am
Manie Verster (6/11/2008)
Mark Beringer (6/10/2008)
The code below will also work, change the ORDER BY according to the column that you want the second row from, I assumed it was the Sal column.WITH Empl AS
(
SELECT
ROW_NUMBER() over(ORDER BY Sal DESC) AS RowNumber,
EmpNo,
EName,
Sal
FROM
Employee
)
SELECT
*
FROM
Empl
WHERE
RowNumber = 2
Hope this helps.
MB
Well, well, well I am amazed. Very good answer there MB. I came here to see if I could help and I learned something new today.
Nice solution.......
June 11, 2008 at 2:53 am
Who needs books & training courses when we have SQL Server Central! Hehe 🙂
June 11, 2008 at 4:22 am
Absolutely, I agree. You get so many things here which will not be available in any one particular book.
Cheers to team.....:)
June 11, 2008 at 4:59 am
This works too:
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 777, 'vvvvv',7879
SELECT TOP 1 * FROM (SELECT TOP 2 * FROM #Employees ORDER BY Sal) ORDER BY Sal DESC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2008 at 9:22 am
Actually, I tried that code and it fails on syntax, due to the ORDER BY clause being present in a subquery. Try the following that uses table variables instead of temp tables:
DECLARE @Employees TABLE (Emp_No INT, EName VARCHAR(5), Sal INT)
DECLARE @Employees2 TABLE (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 777, 'vvvvv',7879
INSERT INTO @Employees2
SELECT TOP 2 Emp_No, EName, Sal
FROM @Employees
ORDER BY Sal
SELECT TOP 1 Emp_No, EName, Sal
FROM @Employees2
ORDER BY Sal DESC
Chris Morris (6/11/2008)
This works too:
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 777, 'vvvvv',7879
SELECT TOP 1 * FROM (SELECT TOP 2 * FROM #Employees ORDER BY Sal) ORDER BY Sal DESC
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
June 11, 2008 at 9:35 am
smunson (6/11/2008)
Actually, I tried that code and it fails on syntax, due to the ORDER BY clause being present in a subquery. Try the following that uses table variables instead of temp tables:Steve
(aka smunson)
:):):)
Nah Steve actually it fails because I missed an important character (the derived table alias) when copying the code to the reply window. This works. Fast too, the principle has been used on this forum before on substantial tables.
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 777, 'vvvvv',7879
SELECT TOP 1 * FROM (SELECT TOP 2 * FROM #Employees ORDER BY Sal) t ORDER BY Sal DESC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2008 at 9:56 am
You're quite right! And that's why you're an "Old Hand", and I'm still an "SSC Rookie". I was able to make the same change to my code with the table variable and it works too. Thanks for the education, as the more often I run into this kind of problem, the more likely I am to learn from it.
Steve
(aka smunson)
:):):)
Chris Morris (6/11/2008)
smunson (6/11/2008)
Actually, I tried that code and it fails on syntax, due to the ORDER BY clause being present in a subquery. Try the following that uses table variables instead of temp tables:Steve
(aka smunson)
:):):)
Nah Steve actually it fails because I missed an important character (the derived table alias) when copying the code to the reply window. This works. Fast too, the principle has been used on this forum before on substantial tables.
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 777, 'vvvvv',7879
SELECT TOP 1 * FROM (SELECT TOP 2 * FROM #Employees ORDER BY Sal) t ORDER BY Sal DESC
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
June 11, 2008 at 10:02 am
smunson (6/11/2008)
You're quite right! And that's why you're an "Old Hand", and I'm still an "SSC Rookie". I was able to make the same change to my code with the table variable and it works too. Thanks for the education, as the more often I run into this kind of problem, the more likely I am to learn from it.Steve
(aka smunson)
:):):)
Not any more Steve, you're a Valued Member! Apologies for the error in my first post.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 16, 2008 at 3:32 pm
How about a little complication to the problem. Write a view that returns second member in the set. So for customers/orders create a view that will return second to last order for every customer...
July 17, 2008 at 3:35 am
CREATE TABLE #Employees (Emp_No INT, EName VARCHAR(5), Sal INT)
delete #Employees
INSERT INTO #Employees (Emp_No, EName, Sal)
SELECT 111, 'AAAA', 9999 UNION ALL
SELECT 112, 'bbbb', 9998 UNION ALL
SELECT 221, 'AAAA', 9997 UNION ALL
SELECT 222, 'bbbb', 9996 UNION ALL
SELECT 331, 'AAAA', 9995 UNION ALL
SELECT 332, 'bbbb', 9994 UNION ALL
SELECT 441, 'AAAA', 9993 UNION ALL
SELECT 442, 'bbbb', 9992 UNION ALL
SELECT 557, 'vvvvv',9991
SELECT TOP 1 * FROM (SELECT TOP 5 * FROM #Employees ORDER BY Sal desc) as emp ORDER BY Sal asc
July 17, 2008 at 10:14 am
The use of a CTE or the following alternative will only perform well on large tables if you have fairly specific index on the ordered column. Also, you could:
Select * from
(
Select *,Row_Number() over(order by SomeColumn)[RId] from dbo.YourTable
) t
where RId=2
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply