Selecting the nth row - Programmers Heaven

Howdy, Stranger!

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

Categories

Selecting the nth row

deostrolldeostroll Posts: 65Member
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?
«1

Comments

  • WEBMASTERWEBMASTER Posts: 549Member
    [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 Posts: 65Member
    Well my table is a non-relational table. There could be fields that have duplicate values.
  • WEBMASTERWEBMASTER Posts: 549Member
    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 Posts: 65Member
    [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:

    select @@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 Posts: 549Member
    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 Posts: 65Member
    [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 Posts: 549Member


    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 Posts: 65Member
    Do you think this problem is solvable by using sub queries alone?
  • WEBMASTERWEBMASTER Posts: 549Member
    What version of SQLServer do you use ? 2000 or 2005?

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

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

    SQL Server 2000
«1
Sign In or Register to comment.