returning table row at specific position (e.g. first, second third)

Posted on 2007-08-09
Last Modified: 2013-11-05

How do i return the nth row in a an ordered table where the row matches a partical criterion

e.g. select nth record from table where column = value

The table is already ordered to support this query. I only really need the first and second rows

thanks a lot
Question by:andieje
    LVL 42

    Expert Comment

    >The table is already ordered to support this query

    Huh? Anyway, doesn't matter--you need to order it.

    Select top 2 * from yourtable where column=value
       ORDER BY colA, colB

    Author Comment

    I obviously didnt make my query clear

    I need the query to return one record only. For example i need the query to return the first record or i need the query to return the second record

    LVL 42

    Accepted Solution

    Assume your table is "ordered" by colA in ascending sequence.

    Two ways to skin the cat:

    Select T3.* from
    Select T1.*
    , (select count(T2.*) from YourTable as T2
       where T2.colA <= T1.colA
      ) as RowNum
     from YourTable as T1
    ) as T3
    where t3.rownum = 2

    Select T1.* from YourTable as T1
      (select count(T2.*) from YourTable as T2
       where T2.colA <= T1.colA) = 2

    Of course it gets a bit messier if you have ties in the order. Let me know.
    LVL 3

    Assisted Solution

    Here a speedy query. Let's assume that YourTable is ordered by Col1. The query below will return the 2nd row.

    SELECT TOP 1 *
          SELECT TOP 2 * -- change the "2" here to specify the "nth" row you need
          FROM YourTable
          ORDER BY Col1
    ) TABLE_A

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now