5

Is it possible to retrieve a specific range of results? I know how to do TOP x but the result I will retrieve is WAY too big and will time out. I was hoping to be able to pick say the first 10,000 results then the next 10,000 and so on. Is this possible?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user1076439
  • 101
  • 4
  • 7

2 Answers2

5

Practically all SQL DB implementations have a way of specifying the starting row to return, as well as the number of rows.

For example, in both mysql and postgres it looks like:

SELECT ...
ORDER BY something -- not required, but highly recommended
LIMIT 100 -- only get 100 rows
OFFSET 500; -- start at row 500

Note that normally you would include an ORDER BY to make sure your chunks are consistent

MS SQL Server (being a "pretend" DB) don't support OFFSET directly, but it can be coded using ROW_NUMBER() - see this SO post for more detail.

Community
  • 1
  • 1
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 5
    It's puzzling why you think not supporting OFFSET makes MS SQL Server a "pretend" DB, because ROW_NUMBER() is ANSI SQL, whereas LIMIT/OFFSET is not. – Bill Karwin Dec 09 '11 at 00:44
  • @BillKarwin Yeah - that comment was emotive, but based on the many horror stories of using MSSQL server... it not being "industrial strength". Like most microsoft server software, they start with single-user software and try to grow it with expected bad consequences. Perhaps they have fixed it in recent years. `ROW_NUMBER()` might be ANSI (didn't know that), but that doesn't make it a good idea. For one, you can't just tack on a tiny bit of SQL to get the rows you want. You have to add columns to the result set and use SQL kung fu to make an incredibly simple thing work. It's really lame IMHO. – Bohemian Dec 09 '11 at 01:29
  • 3
    Well, every RDBMS has some strengths and some weaknesses. I'm no fan of Microsoft, but I understand SQL Server does some pretty amazing stuff with query optimization. It's used for some high-profile websites, including Stack Overflow. :-) – Bill Karwin Dec 09 '11 at 01:37
5
WITH Q AS (
  SELECT ROW_NUMBER() OVER (ORDER BY ...some column) AS N, ...other columns
  FROM ...some table
) SELECT * FROM Q WHERE N BETWEEN 1 AND 10000;

Read more about ROW_NUMBER() here: http://msdn.microsoft.com/en-us/library/ms186734.aspx

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828