MySQL “LIMIT” Equivalent in SQL Server 2005

First of all, there is no direct replacement of MySQL “LIMIT” in SQL Server. Let’s have a look at syntax for “LIMIT” in MySQL Server.

SELECT *
FROM [TABLE NAME]
ORDER BY [ORDER BY COLUMN NAME]
LIMIT 10 OFFSET 100

This query will select 101 to 110 th rows of data in MySQL. In SQL Server "TOP" can be used to select fixed number of rows from a Table or View.

SELECT TOP 10 *
FROM [TABLE NAME]
ORDER BY [ORDER BY COLUMN NAME]

SELECT TOP 10 PERCENT *
FROM [TABLE NAME]
ORDER BY [ORDER BY COLUMN NAME]

TOP 10 PERCENT will dynamically calculate number of rows to be selected.

Again, there is no direct replacement of "OFFSET" in SQL Server. However, you can have something like "LIMIT" and "OFFSET" by querying.

SELECT [COLUMN NAME 1], [COLUMN NAME 2], [COLUMN NAME 3]
FROM 
(
   SELECT *, RANK() OVER (ORDER BY [ORDER BY COLUMN NAME]) As "Serial"
   FROM [TABLE NAME]
)
WHERE Serial BETWEEN [MINIMUM VALUE] AND [MAXIMUM VALUE]

RANK() or ROW_NUMBER() is used to generate row number of a selected record set in SQL Server. If you are not familiar with this you can have look at my posting on Generating Row Number in SQL Server 2005.

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

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

%d bloggers like this: