Microsoft SQL Server | MySQL LIMIT equivalent in Microsoft SQL Server

|
| By Webner

Suppose you want to retrieve values from row from 5 to 10 from a table in SQL server. Since LIMIT keyword does not work in it so we can use any of these 2 alternative methods:

Solution 1 :

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM TableName ) a WHERE row >>5 and row <= 10

Solution 2 :

SELECT TOP 5 * FROM TableName WHERE id NOT IN (SELECT TOP 5 id FROM TableName)

This is same as in MySQL :

SELECT * FROM table LIMIT 5,10; 

First query makes use of ROW_NUMBER() function which returns sequence number of the row (1, 2, 3 etc). Second query discards top 5 tows and then fetches next top 5 to achieve the same.

Webner Solutions is a Software Development company focused on developing CRM apps (Salesforce, Zoho), LMS Apps (Moodle/Totara), Websites and Mobile apps. If you need Web development or any other software development assistance please contact us at webdevelopment@webners.com

Leave a Reply

Your email address will not be published. Required fields are marked *