how to select 2nd toprow from emp table

  • 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.

  • 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

  • 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

  • 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)

  • 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.......

  • Who needs books & training courses when we have SQL Server Central! Hehe πŸ™‚

  • Absolutely, I agree. You get so many things here which will not be available in any one particular book.

    Cheers to team.....:)

  • 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

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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) πŸ™‚ πŸ™‚ πŸ™‚
    Rent Servers for Income (picks and shovels strategy)

  • 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

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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 #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

    Steve (aka sgmunson) πŸ™‚ πŸ™‚ πŸ™‚
    Rent Servers for Income (picks and shovels strategy)

  • 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

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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...

  • 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

  • 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



    PeteK
    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply