Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2014
    Posts
    4

    Question Unanswered: Selecting nth row only (offset)

    I have a query which can return multiple rows. I want to select only the nth row. In other db languages, this is achieved using the OFFSET command, but in SQL Server this command does not exist. I saw that you can hack it using ROWCOUNT and an ORDER BY clause, but I don't have anything to order by. Is there some other way to do this? My query is

    Code:
    SELECT S.SystemName FROM Projects AS P INNER JOIN ProjectSystems AS PS ON PS.ProjectId = P.ProjectId LEFT JOIN Systems AS S ON S.SystemId = PS.SystemID WHERE P.ProjectId = @ProjectID
    This will select an example result of:
    Code:
    System Name
    System 1
    Test System 2
    Test 123
    How can I do something like (psuedo code)
    Select Row @row From query

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The basic rules of set theory apply to SQL databases, so if you don't have anything to order the result set by then the first row returned in the result set is by definition the Nth row. Since rows have no defined order without an ORDER BY clause, as long as a query returns N or more rows then any (and every) row is the Nth row.

    If there is an order to your result set, then the problem is a little bit harder (but not much). Unfortunately, the solution when an order exists depends on what version of SQL Server you are using and how the order is defined.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can number the result set using the ROW_NUMBER() function, wrap a CTE around it, and pick whichever Nth value you choose.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman View Post
    You can number the result set using the ROW_NUMBER() function, wrap a CTE around it, and pick whichever Nth value you choose.
    This is a great specific example of my post above. If there is an ORDER BY that will work with the OVER() clause and the version of SQL Server that aidenkael is using supports Row_Number(), then this is probably the solution that I'd recommend.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I used to use the RAND() function years ago to get every Nth record. Not sure if I still have any of that SQL laying around or not, but I am sure you can google for some.
    Dave

Tags for this Thread

Posting Permissions

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