Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Location
    India
    Posts
    4

    Unanswered: How to return specific Row in sql server

    Hi,

    Suppose I have 100 rows in my table, I want to retrieve say row no 5(I just want any thing on row no 5), I don't want to use identity column. Is there any other way by which I can retrieve specified row.

    Thanks,
    Sajid

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

    the only way to get a specific row is to select it based on the value of one or more of its columns

    if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

    so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence


    rudy
    http://r937.com/

  3. #3
    Join Date
    Jun 2003
    Location
    India
    Posts
    4
    Originally posted by r937
    in relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

    the only way to get a specific row is to select it based on the value of one or more of its columns

    if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

    so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence


    rudy
    http://r937.com/
    Thanks for answering my question.

    Say suppose I have Name column and i want the 5th record on name column.

    Thanks
    Sajid

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Hi

    U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

    Are U sure thats what U want

    Are U Saying you want to select a row based on it's position within a table?

    Ooops r937 U posted whilst I was Posting - I'll Post anyway

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Jun 2003
    Location
    India
    Posts
    4
    Originally posted by GWilliy
    Hi

    U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

    Are U sure thats what U want

    Are U Saying you want to select a row based on it's position within a table?

    Ooops r937 U posted whilst I was Posting - I'll Post anyway

    GW
    Yes I need the same.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select top 1
      from (
    select top 5
           Name
      from yourtable
    order
        by Name
           )
    order
        by Name desc
    rudy

  7. #7
    Join Date
    Jun 2003
    Location
    India
    Posts
    4
    Originally posted by r937
    Code:
    select top 1
      from (
    select top 5
           Name
      from yourtable
    order
        by Name
           )
    order
        by Name desc
    rudy
    Thanks, It works.

    If suppose i want to retrieve all columns, then do I need to specific each column.

  8. #8
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    U could actualy do a sraight select into a cursor & then go straight to the Row

    Code:
    DECLARE @a VarChar(20),@b,@c
    
    DECLARE myCursor CURSOR SCROLL
    FOR
    SELECT a,b,c FROM myTable
    OPEN myCursor
         FETCH ABSOLUTE 5 INTO @a,@b,@c
         BEGIN
             PRINT @a
             PRINT @b
             PRINT @c
        END
    CLOSE myCursor
    DEALLOCATE myCursor
    example Code to give U an Idea - There are many options available when declaring Cursors etc.

    May be more suitable for whatever Sajidrep is Doing

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •