locked
Sql question( select the first, second row in the table) RRS feed

  • Question

  • User1941939432 posted
    In MS Sql server, no "first" function,

    how to select only the first row?

    By the way, how to select only the seoncd row?

    Thx
    Friday, July 1, 2005 12:27 PM

All replies

  • User-1786411686 posted
    There is no particular order in which SQL stores the records. You need to specify the ORDER BY when you are talking about first/second etc. So if you want only first rowof a particular column you can do
    SELECT TOP 1
       <col1>,
       <col2>
    FROM
       <table>
    ORDER BY
       <column> ASC|DESC
    For second row there a couple of ways: Lets say you have some date column and you want the 2nd latest row. One way could be :
    SELECT
       MAX(<datecolumn>),
       <other columns>
    FROM
       <table>
    WHERE
       <datecolumn> < ( SELECT MAX(<datecolumn>) FROM <table>
    Friday, July 1, 2005 1:09 PM
  • User704942467 posted
    There is the "TOP" keyword.

    SELECT TOP 1 *
    FROM myTable
    ORDER BY ID

    This will return the top ( or first ) record in the table ordered by the ID.

    There are several, several ways to get the second row.  Here is one

    SELECT TOP 1 *
    FROM myTable
    WHERE ID NOT IN ( SELECT TOP 1 ID FROM myTable ORDER BY ID )
    ORDER BY ID

    This says to get the first record that isn't the first record.  ie the second record.

    bill
    Friday, July 1, 2005 1:13 PM
  • User1941939432 posted
    THX
    Friday, July 1, 2005 1:23 PM
  • User39490543 posted
    I've always solved the "second row" problem like this:
    SELECT TOP 1 *
    FROM
    (
    SELECT TOP 2
       <some columns>
    FROM
       <table>
    ORDER BY
       <something> ASC
    )
    ORDER BY <something> DESC

    I'm not sure if it will perform better or worse than the other proposed solutions, although I suspect it might be better
    Friday, July 1, 2005 2:37 PM
  • User727029355 posted

    I tried all three of those methods hoping one would work for my similar problem.  Each of them returned the wrong result.  All the last one does is return the top record, NOT the next record.  The other two methods each returned the THIRD record instead of the second.

    Any other ideas out there...

    Thursday, February 16, 2006 6:14 PM
  • User-1225738063 posted

    I'm guessing that when you are ordering by ID, that both the first and second records have the same ID?  Use tmorton's suggestion, and add more columns to the order by clause, enough to guarantee a unique result, or add all the columns if you aren't sure.

    Thursday, February 16, 2006 11:12 PM
  • User604186779 posted

    Hi

    i want to get the data what i have added first record in my database

    like this

    how shall I retrieve the second record from database ie; second record

    can anyone tell me..?

    this is my Query how to get the top second record

    Query:

    here contentid is the primary key


    SELECT * From content where subcategoryid=@subcategoryid order by contentid desc

    I have worked ur Query its not working..

    can u clarify the query pls

    Thanks

    Wednesday, July 23, 2008 9:26 AM
  • User604186779 posted

    i used ur query this is the query

    Dim selectpageimage_cmd As New SqlCommand("SELECT TOP 1 * from content where contentid not in(SELECT top 1 * From content where subcategoryid=@subcategoryid )", con)

     It show error:

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

    Wednesday, July 23, 2008 9:42 AM
  • User1987365881 posted

     

    Hi,

    Just wanted to share with you the ones that worked fine for me :

    ------------------------------------------------

    TO GET 4th HIGHEST SALARY IN EMP:

    select top 1 sal from EMP where sal <
    (select MAX(sal) from EMP where sal <
    (select MAX(sal) from EMP where sal <
    (select MAX(sal) from EMP)))order by sal desc

    ------------------------------------------------

    TO GET 3rd HIGHEST SALARY IN EMP:

    select top 1 sal from EMP where sal <
    (select MAX(sal) from EMP where sal <
    (select MAX(sal) from EMP))order by sal desc

    -------------------------------------------------

    TO GET 2nd HIGHEST SALARY IN EMP:

    select top 1 sal from EMP where sal <
    (select MAX(sal) from EMP)order by sal desc

    ------------------------------------------------

    TO GET 1st HIGHEST SALARY IN EMP:

    select top 1 sal from EMP order by sal desc

    ------------------------------------------------

     

    Thanks,

    Srinath

    (Let me know if it fails for one reason or the other)

    Wednesday, June 24, 2009 4:19 AM
  • User1545643680 posted

    Please reffer following site to solve your problem.

       http://aspalliance.com/1885_Selecting_Records_Based_on_Row_Number_in_SQL

    Thanks

    Shriram gore

    Thursday, November 5, 2009 5:57 AM
  • User-331346503 posted

    I've always solved the "second row" problem like this:

    SELECT TOP 1 *
    FROM
    (
    SELECT TOP 2
       <some columns>
    FROM
       <table>
    ORDER BY
       <something> ASC
    )
    ORDER BY <something> DESC


    I'm not sure if it will perform better or worse than the other proposed solutions, although I suspect it might be better

    seems to be a nice solution....

    Thursday, November 5, 2009 6:04 AM
  • User1545643680 posted

    Hello,

       You are right ,But if there is only one record then it will return wrong data.

    Thats why we need to fetch record according to row count.

    SELECT *
    FROM(
        SELECT ROW_NUMBER() OVER (ORDER BY Salary DESCAS SrNo, 
                EId,EmployeeName, Salary 
        FROM 
            EmployeeDetails
        )    AS EMPLOYEE
    WHERE
        SrNo=2 //this is row no. You can take any no of row data from here.
    Thursday, November 5, 2009 6:34 AM
  • User13878136 posted

    i used ur query this is the query

    Dim selectpageimage_cmd As New SqlCommand("SELECT TOP 1 * from content where contentid not in(SELECT top 1 * From content where subcategoryid=@subcategoryid )", con)

     It show error:

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS


    It has to be (contentid instead of *)

    SELECT TOP 1 * from content where contentid not in(SELECT top 1 contentid From content where subcategoryid=@subcategoryid )


    Thursday, November 5, 2009 9:51 AM
  • User1045724458 posted

    Hi Bill,

    "WHERE ID NOT IN()"   ---ROCKS...cool... It got me an extra buffet. Hence thanks and credits to you.

    Thanks buddy,

    Suresh Kumar V.C

    Wednesday, April 29, 2015 8:05 AM
  • User-105640681 posted

    HI FELLAS!!

    JUSt want to let you know that i have a very straight and perfect answer of above mentioned question i.e.

    ( select * from (select *, ROW_NUMBER() OVER (ORDER BY {COLUMN NAME) ) rn FROM [TABLE NAME] ) AS [ALIAS] WHERE rn = 2 )

    ABOVE MENTIONED QUERY WILL FETCH THE 2ND ROW FROM THE TABLE AND LET'S SUPPOSE, YOU WANT TO RETRIEVE 10TH ROW FROM THE TABLE THEN ONLY A SMALL CHANGE YOU HAVE TO MAKE IN THE QUERY LIKE REPLACE RN = 2 BY RN = 10 AND THEN YOU  WILL SEE THE CHANGES 

    THANKS 

    AAYUSH 

    Friday, July 3, 2015 4:04 PM