Welcome to another episode of “blogging to remember something that I don’t do often enough to remember off the top of my head but now I’ll remember to go search my blog for the answer the next time that I have to do this”. Today’s episode involves coming up with the equivalent of LIMIT from MySQL and PostgreSQL in Microsoft SQL Server (2000 in this case, I’m not sure if this has changed in 2005). Although the LIMIT syntax doesn’t appear to be part of the SQL standard syntax, I prefer it over the more verbose methods that are mentioned in the standard. Using LIMIT is easy and very handy, I wish other database vendors would pick up on it.
SQL Server has a clause called TOP that looks like this:
SELECT TOP n * FROM tablename ORDER BY key
This is fine, but it doesn’t support offsets. To get the TOP N rows with an offset you’ll have to make use of a sub-select along the lines of:
SELECT TOP n *
FROM tablename
WHERE key NOT IN (
SELECT TOP x key
FROM tablename
ORDER BY key
)
So listen up Oracle, DB2 and SQL Server, the LIMIT clause is a lot easier to use so set egos/NIH (not invented here) aside and adopt the LIMIT syntax. Please. Pretty please. Pretty please with sugar on TOP.
UPDATE 23 May 2006 @ 10:30am : For those of you who aren’t familiar with the LIMIT clause in PostgreSQL and MySQL it allows you to limit the number of rows returned. To get the first 10 results you’d something like this:
SELECT * FROM tablename ORDER BY key LIMIT 10
But what if you app was paging results sets, displaying 10 at a time? With LIMIT you can provide an OFFSET that allows you to skip ahead. Here is an example of getting another 10 rows, with an OFFSET of 100:
SELECT * FROM tablename ORDER BY key LIMIT 10 OFFSET 100
Simple and to the point. Thanks to Scott (see comment #1) for reminding me that not everyone reading this was already familiar with the LIMIT clause.