MSSQL 2000 emulate MySQL "limit"

This question has been discussed on many articles. You can even google many results with different solutions. However, most solutions use 3 levels query to complete this emulation. Although those solutions can emulate the “limit” function on MySQL, yet the performance is another problem. 

I am not sure about this solution if it has been anounced before. I engage a problem in MSSQL 2000 that I need to use the equivalent function like “limit” in MySQL. Here is my solution with only 2 levels. For example, if you want to select the top 30 records(rows) from the table “account”, in MySQL, you will use

SELECT * FROM `account` WHERE 1 LIMIT 0 , 30

the first parameter 0 means the start position; 30 means the offset. 
Say if you have 1000 records, you want to select the top 200 records but exclude the top 100 from table “account”, SQL in MySQL will be

SELECT * FROM `account` WHERE 1 LIMIT 100 , 100

Ok, time to go back to MSSQL. How to emulate this function in MSSQL 2000 ? The answer is using “top” function. The following is the answer for the first query in MySQL, 

SELECT TOP 30 * FROM account 
WHERE
    ID IN (SELECT TOP 30 ID FROM account)
AND
    ID NOT IN (SELECT TOP 0 ID FROM account)

I need to clarify few points, 
1. ID must be a PK(Primary Key)
2. In the outside (first query), top 30 means you want to get 30 records; the first sub-query means you want all top 30 records from the same table that is exact the same ID as you get in the outside query and get all top 0 record(s) from the table that is not the same ID as you get in the outside query. 

How about to emulate the second MySQL in MSSQL ?

SELECT TOP 100 * FROM account 
WHERE 
    ID IN (SELECT TOP 200 ID FROM account) 
AND 
    ID NOT IN (SELECT TOP 100 ID FROM account)

You should understand this SQL very quickly. You want 100 records, these 100 records come from 100~200 using ID as a filter. Thus, this SQL can emulate the limit function completely (Although this only work on the table with PK). 

So, 

SELECT TOP 250 * FROM account 
WHERE 
    ID IN (SELECT TOP 1500 ID FROM account) 
AND 
    ID NOT IN (SELECT TOP 1250 ID FROM account)

will be the emulation of MySQL

SELECT * FROM account WHERE 1 LIMIT 1250 , 250

=>

MSSQL 2000

SELECT TOP offset * FROM account 
WHERE 
    ID IN (SELECT TOP (start_position + offset) ID FROM account) 
AND 
    ID NOT IN (SELECT TOP start_position ID FROM account)

will be the emulation of MySQL

SELECT * FROM account WHERE 1 LIMIT start_position , offset

 

You are welcomed to leave your comments!!

Advertisements

2 thoughts on “MSSQL 2000 emulate MySQL "limit"

  1. PS: The application can be used in writing the webpage showing a lot of transactions/records in pages. Say you have 20,000 rows, you want to show 100 rows in 200 pages, then it’s show time!

  2. Not that I’m totally impressed, but this is a lot more than I expected for when I found a link on Digg telling that the info here is quite decent. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s