the MS SQL equivalent to MySQL's 'limit' command

The MS SQL eqivalent of MySQL's LIMIT clause is TOP. Example:

SELECT TOP 10 * FROM stuff;

Will return the top ten rows, effectively doing the same thing as

SELET * FROM stuff LIMIT 10;

This was found here and has been verified by me that it actually works.

Posted by dustin on February 11, 2004 with category tags of

36 comments
My thoughts exactly.

   comment by chrisdye (#15) on February 12, 2004

thanks you helped me like nythin
   comment by SUNIL KARTIKEY on September 21, 2004

I think anybody can get to like nythin if they give it a chance.
   comment by dye! on September 23, 2004

well almost,

starting with MySQL 4 you can do LIMIT 10, 1000

which will give you a block of 1000 records starting at 10

is there anything similar to this in sql server? i don't think so.
   comment by andrew on August 22, 2005

limit x, y - MySQL

MSSQL version :
select top y *
from <table>
where <unique id> not in (select top x <unique id> from <table> order by <unique id>)
from <table> order by <unique id>
   comment by Rustans Cubao on March 2, 2006

there are at least 2 solutions:

select top * from ( select top * from table order by column )
order by column

and

select * from
(
select top * from
(
select top (+) from table order by column ASC
) T1 order by column DESC
) T2 order by column ASC

I "think" the first is better
   comment by florin on June 5, 2006

Y si lo manejamos con el between?

where id between x and y
?
   comment by Juan Pablo on June 26, 2007

Juan Pablo, el "between" no funciona sobre MSSQL...

Hasta luego !
   comment by olag4277 on July 10, 2007

Florin,
Don't think your solution will work, well the first one doesn't. If I want records 1 through 5 or 0 through 5 it doesn't account for that.
   comment by Anonymous on July 24, 2007

How you can do it is described on
http://www.select-sql.com/mssql/how-to-make-limit-from-to-in-mssql-2005.html
   comment by Stanislav Duben on August 22, 2007

This method doesn't support ordering over calculated fields! Any work around?
   comment by Broken Arrow on November 18, 2007

SUPPOSE THERE IS A TBALE AS MARKLIST LIKE
TABLE : MARKS
IDNAMETOT_MARKS
1ANDREWS50
2 SYMONDS 25
3 MATHEW 70
4 ROY 40
5 MARY 35
6 CLINTON 65
7 MARCEL 80
8 PABLO 60

AND YOU WANT TO SELECT 3RD TO 5TH TOP SCORERS

YOU USE :-

SELECT TOP 3 * FROM MARKS WHERE ID NOT IN (SELECT TOP 2 * ID FROM MARKS ORDER BY TOT_MARKS) ORDER BY TOT_MARKS


OUTPUT WILL BE->
IDNAMETOT_MARKS
3 MATHEW 70
4 ROY 40
5 MARY 35
I THINK IT WILL HELP THOSE HAVE THE SAME DOUBT

   comment by VINOD KUMAR on February 16, 2008

SUPPOSE THERE IS A TBALE AS MARKLIST
LIKE

TABLE : MARKS
-------------
ID------NAME------------TOT_MARKS
---------------------------------
1-------ANDREWS---------50
2-------SYMONDS---------25
3-------MATHEW----------70
4-------ROY-------------40
5-------MARY------------35
6-------CLINTON---------65
7-------MARCEL----------80
8-------PABLO-----------60
---------------------------------
AND YOU WANT TO SELECT 3RD TO 5TH TOP SCORERS

YOU USE :-

SELECT TOP 3 * FROM MARKS WHERE ID NOT IN (SELECT TOP 2 * ID FROM MARKS ORDER BY TOT_MARKS) ORDER BY TOT_MARKS

OUTPUT WILL BE->

ID------NAME------------TOT_MARKS
---------------------------------

3-------MATHEW----------70
4-------ROY-------------40
5-------MARY------------35

---------------------------------
   comment by VINOD KUMAR on February 16, 2008

Its very useful for me Thanks very much.
   comment by Manosh k.v. on April 1, 2008

SELECT *
FROM (SELECT TOP (3) *
FROM (SELECT *
FROM (SELECT TOP (5) *
FROM cisla
ORDER BY id)
ORDER BY id DESC)
ORDER BY id DESC)
ORDER BY id
   comment by martin on July 9, 2008

rows from 3 to 5, in table called "cisla" and column is named "id", its PK
   comment by martin on July 9, 2008

and the shorter version.. i think, that one version like this is above:

SELECT *
FROM (SELECT TOP (3) *
FROM (SELECT TOP (5) *
FROM cisla
ORDER BY id)
ORDER BY id DESC)
ORDER BY id

"cisla" is the name of table, "id" is column
   comment by martin on July 9, 2008

MSSQL's solution for MySQL limit is a joke.
   comment by Con on April 16, 2009

Thank you guys this was really very appreciated It helped a lot

cheers
   comment by sam on August 24, 2009

Not very helpful to be honest. Limit will get a RANGE of records from a database but TOP only gets the top x records.
   comment by David Levin on March 9, 2010

I agree, MSSQL is a joke. I need a LIMIT 1 for an UPDATE.
   comment by Calvin on April 12, 2010

Here's Proper Solution..

WITH ORDERED AS (
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber, ID, NAME, TOT_MARKS FROM [MARKS]
)
SELECT ID, NAME, TOT_MARKS FROM ORDERED WHERE RowNumber >= 3 AND RowNumber <=5
   comment by Jay Pavagadhi on May 19, 2010

Hey Dustin, What makes you random?
   comment by Jon on June 9, 2010

everything
   comment by dustin (#1) on June 9, 2010

Awsome!

I can't always remember all of the DBMS syntax that I have to work with. My favorite thing about MS SQL is that it works so that I can meet my clients requirements and get paid.

Cheers.
Jon
   comment by Jon on June 9, 2010

MS SQL stupid !
   comment by odai jawasreh on September 12, 2010

Why spend all the $$ to use MS software when open source software is available for a free download and usually has fewer problems than anything MS?
   comment by John Bowling on November 14, 2010

Thanks this helps a lot.
   comment by Aalok Parikh on December 9, 2010

This is MSSQL Damn! I cant believe people cant see the difference...

Microsoft SQL Server

If you want the MYSQL you need to use:

SELECT * FROM tbl LIMIT 10;
# Retrieve first 10 rows

Regards
   comment by gabriel on April 24, 2011

Hey guys....
u try to assess MS SQL with that silly limt case???
   comment by Sudhi on May 3, 2011

John Bowling:
sometimes, we are required to use mssql by our workplace

If I had it my way, I would always use MySql, however, most non-technical businesses tend to go the all MS route. I am just happy I was atleast able to use PHP instead of ASP!
   comment by green daemon on July 15, 2011

Thanks!!! a lot
   comment by Karthik on September 2, 2011

SELECT * FROM (select row_number() over (order by ID) as rownum, ID, NAME, TOT_MARKS FROM [MARKS]
) as A where A.rownum BETWEEN (3) and (5)
   comment by Dave Sealey on November 22, 2011

what's wrong with ms sql?!! wtf!
   comment by LOL on March 20, 2012

Is der any clause called "skyline of" in sql?
   comment by ritu on January 12, 2014

Commenting is currently off. You can email the author.

   

VorgTag CloudArchives

Written by dustin
Photo Showcase
Quote Showcase
Recent Comments
Friends of Vorg
Popular Posts

Hey You! Subscribe to dustin's RSS feed.
Or get wider opinion in the Vorg All Author feed.

 
 

Members login here.
© Vorg Group.