Results 1 to 4 of 4

Thread: single record

  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: single record

    Is there a way to pick just the 500th record in a result set without using a cursor or doing it on the client. I wont know anything about the record to use where...

    example:

    firstname lastname tot
    bill clinton 0
    george bush 1
    someone else 2
    big bird 3

    I want to pick the 3rd record, but I can't use a where because I don't know what the values of the 3 record are.

    Hope I made this clear.

    Thanks in advance for your help.

  2. #2
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    Hi you have to use 2 TOP clauses together:

    SELECT TOP 1 * FROM (SELECT TOP n * FROM table) AS table2 ORDER BY id DESC

    in this query "n" is the row you what to access to. So, in you case it will be 3.

    Of course, in order to function properly, this solution need that the table is ordered in some manner. In this case i've choosen the "id", you can choose "tot"

  3. #3
    Join Date
    Jan 2003
    Posts
    67

    Thanks!

    That's what I needed.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    manowar's solution is the most efficient if you know the ordinal position you want ahead of time. Unfortunately, you cant supply a variable to the TOP clause without dropping into dynamic sql statements. This method is probably slower, but will get you any ordinal position:

    declare @OrdinalValue integer
    set @OrdinalValue = 50

    select YourTable.PrimaryKey
    from YourTable
    inner join YourTable YourTableAlias on YourTable.SortField > YourTableAlias.SortField
    group by YourTable.SortField
    having count(*) = @OrdinalValue

    blindman

Posting Permissions

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