TOP - a LIMIT work around for SQL Server
Lukas on Mar 2nd 2008
Microsoft’s SQL Server has no implementation of the LIMIT clause as it is known from mySQL. This article is intended to explain a method using the TOP clause to imitate the behaviour of LIMIT.
Quick Answer:
Explanation:
Suppose you have a table called languages as shown below, with a single column named name listing esoteric programming languages.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | |------------| |name | |------------| |Argh! | |AutoMouse | |BackFlip | |Brainfuck | |COW | |DOG | |evil | |FALSE | |GOTO++ | |HELP | |l33t | |MonkeyCode | |NULL | |Ook! | |Qwertycode | |Random | |Shakespeare | |Trigger | |Underload | |Whitespace | |ZOMBIE | |------------| |
Now let us assume that you would like to show 5 records from this table, starting with the 11th one, which is l33t.
In SQL flavours supporting LIMIT this would could be easily done like so:
SELECT name FROM languages LIMIT 10, 5;
However, since in SQL Server only TOP is available, we require something more elaborate to achieve the same result.
The main problem is that TOP does not allow for an offset to be defined, so something like TOP 10, 5 will not work.
The solution requires a sub-select statement that retrieves the records that come before the beginning of our desired range of rows. In this case that would be the rows starting with Argh! and ending with HELP. To do so we use TOP in our SELECT statement like so:
SELECT TOP 10 name FROM languages;
Now that we have a list of values that come before the rows that we want to extract we can use this list in a SQL query to get it to be skipped:
SELECT name FROM languages WHERE name NOT IN (SELECT TOP 10 name FROM languages);
In this example only those records are selected whose name does not appear amongst the top 10 rows. In other words, we now have a list going from 133t to the very end of the table, i.e. ZOMBIE.
All we need to do now is to specify the records we are interested in using another TOP statement:
SELECT TOP 5 name FROM languages WHERE name NOT IN (SELECT TOP 10 name FROM languages);
Note that I added TOP 5 to the outer SELECT clause, thereby only selecting the first 5 rows of the list that (thanks to the sub-select) starts with l33t.
This shows that we have to use a nested SELECT where the outer TOP specifies the number of rows you want to retrieve, and the inner TOP specifies the starting row.
A Note of Caution:
Do realise that this work around is a lot less efficient than the actual LIMIT clause. I therefore wouldn’t advise to use it on large sets of data.
Filed in SQL | 6 responses so far
Milky Mar 4th 2008 at 02:08 pm 1
How would i be able to set it up so that i can click Next and see the next ten records within the database?
Lukas Mar 4th 2008 at 02:18 pm 2
You would set the next/previous page links up so that they pass the page number via GET.
You then use your server-side script to multiply the page number you want to display by the range. The range is then subtracted from the result of the multiplication, leaving you with the value needed for the inner TOP.
For example, if you want to view page 3 and you have set it to display 10 items at the time then you would calculate 3 * 10 - 10 = 20.
Keeping in mind that the outer TOP defines the range you now have both all values needed to run the query.
Lynsay Mar 7th 2008 at 09:26 am 3
Just figured this out on Wednesday- I was trying to use LIMIT, not realising that it wasn’t compatible with MSSQL. Would have been useful if PHP had displayed a proper error!
Milky Mar 7th 2008 at 03:13 pm 4
where would you add an extra Where clause that defines words with the letter ‘e’ in them to be showed?
Ryan Jul 6th 2008 at 12:31 am 5
“Just another Tech Blog”
Ah! but blogs can be like seeds and your help is…helpfull!
Thanks for this one
Vijaycanaan Sep 30th 2008 at 12:51 pm 6
Very kind hearted by helping people like me. Blog is simple and effective.