Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: return the value of the 5000th record in iteration

    I want an elegant way to retrieve each iteration of the value of the 5000th record from a table with a clustered PK int column name: ID

    so the 5000th, 10000th, 15000th, etc... until as many records in the table

    -- for example this will get me the 1st
    SELECT TOP 1 Q1.Id FROM
    (
    SELECT TOP 5000 Id FROM myTable T
    ORDER BY T.Id
    ) Q1
    ORDER BY Q1.Id DESC
    Last edited by Gagnon; 10-11-10 at 18:52.

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    I figured it out:
    Code:
    ; WITH OrderedIds AS
    (
        SELECT Id,
        ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber'
        FROM dbo.MyTable
    ) 
    SELECT Id 
    FROM OrderedIds
    WHERE RowNumber % 5000 = 0;

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Elegant and simple solution. Nice.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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