› ›

Selecting the nth row

deostrolldeostroll
in SQL-Server
I want to select the nth row from a table. But as of now I do not know how to hatch the query for it. I have the logic.

1. Select the top n records (record set 1)
2. Select the top n-1 reocrds (record set 2)
3. select from the table where records lies in record set 1, but now in record set 2.

This logic isolates the nth record. How do you write the query for it?

Comments

  • WEBMASTERWEBMASTER
    [b][red]This message was edited by WebMaster at 2007-3-4 10:25:6[/red][/b][hr]
    If you are using SQLServer 2005 then you could use the Row_Number

    http://weblogs.asp.net/Firoz/archive/2005/06/12/411949.aspx

    if you are using SQL Server 2000 then you can try to use the ROWCOUNT to do this

    try

    http://www.codeproject.com/useritems/SQLServer2KPagingSorting.asp

    http://scottonwriting.net/sowblog/posts/5992.aspx



    [blue][italic][b]/WEBMASTER[/b][/italic][/blue]



  • deostrolldeostroll
    Well my table is a non-relational table. There could be fields that have duplicate values.
  • WEBMASTERWEBMASTER
    What Database are you using? RowCount is independent of duplicate value if you are using SQL-Server.

    give it a try

    [blue][italic][b]/WEBMASTER[/b][/italic][/blue]

  • deostrolldeostroll
    [b][red]This message was edited by deostroll at 2007-3-7 11:15:56[/red][/b][hr]
    I've tried using the row count like this:

    @rowcount

    But this variable holds the value of the number of records processed in the previous operation. So if you execute a select command to query a table, and after that you execute the above statement you will see the number of records that was actually returned...

    PS: Could you tell me the meaning of paging...


  • WEBMASTERWEBMASTER
    Hi read http://scottonwriting.net/sowblog/posts/5992.aspx

    You can do a
    SET RowCount 10

    instead of doing a SELECT RowCount.

    like

    SET RowCount 10
    SELECT * from Table

    use SET ROWCOUNT to just get the next n records after (and including)
    that ID.

    Paging means that you get XX rows from a table at postion YY.

    To get record n, then that could look like get page at postition
    N with a page size of 1.

    You can also read
    http://www.4guysfromrolla.com/webtech/042606-1.shtml




    [blue][italic][b]/WEBMASTER[/b][/italic][/blue]

  • deostrolldeostroll
    [b][red]This message was edited by deostroll at 2007-3-7 14:19:15[/red][/b][hr]
    : Hi read http://scottonwriting.net/sowblog/posts/5992.aspx

    Ok thanks, I'll post back with my ideas later...Btw could you tell me what rowcount is...?


  • WEBMASTERWEBMASTER


    TOP vs. SET ROWCOUNT
    The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in the following ways:

    The SET ROWCOUNT limit applies to building the rows in the result set after an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement is ended when n rows have been selected from a set of values that has been sorted according to specified ORDER BY classification.


    The TOP clause applies to the single SELECT statement that it is specified in. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.

    [blue][italic][b]/WEBMASTER[/b][/italic][/blue]

  • deostrolldeostroll
    Do you think this problem is solvable by using sub queries alone?
  • WEBMASTERWEBMASTER
    What version of SQLServer do you use ? 2000 or 2005?

    [blue][italic][b]/WEBMASTER[/b][/italic][/blue]

  • deostrolldeostroll
    : What version of SQLServer do you use ? 2000 or 2005?

    SQL Server 2000
  • WEBMASTERWEBMASTER
    To answer some of your questions

    1. SELECT TOP 10 * from Table1

    1. SELECT TOP 9 * from Table2

    3. SELECT * FROM Table1 WHERE ID NOT IN
    (SELECT ID from Table2)



    >>This logic isolates the nth record. How do you write the query for it?

    Can you not just select a specific ID? or is the ID range not complete?

    like
    SELECT * FROM table2 WHERE ID=nnnn?

    Selecting the absolute Nth record regardless of ID is a bit more complex.

    One way is to;

    You could select the N first rows in the table using TOP and store it into a temporary table. Then select the last one from that table like

    like (psuedo code)

    SELECT top N ID from Table1 INTO Temptable /* Just store ID records in temptable */

    SELECT * FROM Table1 WHERE ID IN
    (SELECT TOP 1 ID From TempTable ORDER BY ID DESC)


    See help on how to create a temporary tabl
    e. using temporary Table might not be the best if N is very large.

    Hope this helps.




    [blue][italic][b]/WEBMASTER[/b][/italic][/blue]

  • deostrolldeostroll
    [blue]
    : 1. SELECT TOP 10 * from Table1
    :
    : 1. SELECT TOP 9 * from Table2
    :
    : 3. SELECT * FROM Table1 WHERE ID NOT IN
    : (SELECT ID from Table2)
    [/blue]

    The problem is that there may be duplicate IDs. So in that case somehow a select distinct should work. The idea is that if there are duplicates and one of them happens to lie in the row we are fetching, then show only one record! If you are considering the subject of duplicates there may be two or more records that are same...

    And the rule is you can't use temporary tables :-(

    I don't think there is a query-only solution for this. However the 'set rowcount' thing works for the moment.

    Well the story behind why I ask this particular task is my senior gives me interesting challenges. For one of the challenges, I misunderstood one of the question. And the result of that was this question, selecting the nth row...
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Categories

In this Discussion