Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: SELECT TOP N Question?

    SQL Server supports the syntax:

    SELECT TOP N ... to get the TOP n rows from a query.

    Is there anyway to do
    SELECT TOP N SKIP N
    or
    SELECT MIDDLE N
    or something to that effect? I am dealing with large data sets (20-50 million records) and would like to be able to query 50K records at a time and would like to find a solution that doesn't need an ORDER BY clause so the query stays fast. I also need to support closing the database connection and restarting the client application between 50K chunk.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, I'll preface this by saying that it is dirty, low-down, high-smelling, and those are it's good points!

    Try:
    Code:
    SELECT Cast(NULL AS INT) AS batchId, PK
       INTO dbo.junque
    
    DECLARE @c INT, @i INT
    
    SET ROWCOUNT 50000
    
    SELECT @i = 1, @c = 1
    
    WHILE 0 < @c
       BEGIN
          UPDATE dbo.junque SET batchId = @i WHERE batchId IS NULL
          SELECT @c = @@rowcount, @i = 1 + @i
       END
    
    SET ROWCOUNT 0
    This should give you your primary keys divided into batches of an arbitrary size. You can then join back into your original table using the PK, working from the batch ids. Not perfect, but better than a poke in the eye with a sharp stick!

    -PatP

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by Pat Phelan
    Ok, I'll preface this by saying that it is dirty, low-down, high-smelling, and those are it's good points!
    This is building a temp table with BatchID/PK mapping and using that to query a specific batch number from, right?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by RogerWilco
    This is building a temp table with BatchID/PK mapping and using that to query a specific batch number from, right?
    Yep, that's exactly what I'm suggesting.

    -PatP

Posting Permissions

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