Categories
josephscott

SQL Server Equivalent To MySQL And PostgreSQL Limit Clause

Welcome to another episode of “blogging to remember something that I don’t do often enough to remember off the top of my head but now I’ll remember to go search my blog for the answer the next time that I have to do this”. Today’s episode involves coming up with the equivalent of LIMIT from MySQL and PostgreSQL in Microsoft SQL Server (2000 in this case, I’m not sure if this has changed in 2005). Although the LIMIT syntax doesn’t appear to be part of the SQL standard syntax, I prefer it over the more verbose methods that are mentioned in the standard. Using LIMIT is easy and very handy, I wish other database vendors would pick up on it.

SQL Server has a clause called TOP that looks like this:

SELECT TOP n *
FROM tablename
ORDER BY key

This is fine, but it doesn’t support offsets. To get the TOP N rows with an offset you’ll have to make use of a sub-select along the lines of:

SELECT TOP n *
FROM tablename
WHERE key NOT IN (
    SELECT TOP x key
    FROM tablename
    ORDER BY key
)

So listen up Oracle, DB2 and SQL Server, the LIMIT clause is a lot easier to use so set egos/NIH (not invented here) aside and adopt the LIMIT syntax. Please. Pretty please. Pretty please with sugar on TOP.

UPDATE 23 May 2006 @ 10:30am : For those of you who aren’t familiar with the LIMIT clause in PostgreSQL and MySQL it allows you to limit the number of rows returned. To get the first 10 results you’d something like this:

SELECT *
FROM tablename
ORDER BY key
LIMIT 10

But what if you app was paging results sets, displaying 10 at a time? With LIMIT you can provide an OFFSET that allows you to skip ahead. Here is an example of getting another 10 rows, with an OFFSET of 100:

SELECT *
FROM tablename
ORDER BY key
LIMIT 10 OFFSET 100

Simple and to the point. Thanks to Scott (see comment #1) for reminding me that not everyone reading this was already familiar with the LIMIT clause.

15 replies on “SQL Server Equivalent To MySQL And PostgreSQL Limit Clause”

I guess I don’t really understand the purpose of the LIMIT clause that you mention. Do you have a code sample of the equivalent code in MySql? Offhand, I’m wondering whether SET ROWCOUNT would help but I doubt it would… I’m just not clear on what you’re trying to do without a code sample.

I’d like to add something about “limit” on PostgreSQL:

select * from foo limit 10 order by bar;

is not valid – you have to use:

select * from foo order by bar limit 10;

Hi friends, im displaying set of records from SQL server 2000 DB. I want to display 5 records per page. Can anyone help me, how to write query to take first 5 records and next and so on..

I tried using Limit statement.

select * from phaseblock order by block Limit 5;

but its not working..Thankz in advance.

Hi, that’s a slick bit of code compared with some MS SQL pagination stuff I’ve seen out there! There is an error in it though, the “FROM tablename” on the last line with the ORDER clause is not required.

Is there any way of doing this without having to make the query again? What happens when the query has a considerable complexity and performance impact?

@srinivasan:
Did you even read the post?

Actually, regarding the comment on DB2, IBM actually supports the “FETCH” clause. It’s not quite as robust as MySQL’s “LIMIT” (it doesn’t provide support for a starting offset), but I find it to be very useful, especially if you want to sort the results of an aggregated sub-query by one of your calculated fields. Here’s an example of its usage:

SELECT * FROM
(SELECT YEAR, MONTH, STYLE, SUM(GROSS_SALES) AS GROSS,
SUM(NET_SALES) AS NET
FROM DB.SALES WHERE YEAR=2008 AND MONTH=3
GROUP BY YEAR, MONTH
) AS TABLE
ORDER BY NET DESC
FETCH FIRST 10 ROWS ONLY

The above would give you the top ten styles in net sales for March-2008.

I agree, the other players need to swallow their pride and adopt “LIMIT”, but “FETCH” is nice too for DB2 users 🙂

Exact solution for SQL Server Equivalent To MySQL LIMIT clause
following is the stored procedured that i have created for this purpose
by creating a temp table and then filter the records from it by row index

Here is the stored procedure as follows

— ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:
— Create date:
— Description:
— =============================================
Alter PROCEDURE Test
— Add the parameters for the stored procedure here
@page int = 0,
@count int =10,
@ProductCategoryId int,
@ProductSubCategoryId int,
@IsDeleted bit,
@IsEnabled bit
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

— Insert statements for procedure here

SET NOCOUNT ON;

CREATE TABLE #TempTBL (
[ProductCategoryId] [int] NOT NULL,
[ProductSubCategoryId] [int] NOT NULL,
[ProductId] [int] NOT NULL,
[ProductName] [nvarchar](50) NULL,
[ProductSupplier] [nvarchar](50) NULL,
[ProductShortDescription] [nvarchar](100) NULL,
[ProductModel] [nvarchar](50) NULL,
[ProductLongDescription] [nvarchar](500) NULL,
[ProductRRP] [money] NULL ,
[ProductNowRate] [money] NULL ,
[ProductMake] [nvarchar](50) NULL,
[ProductType] [nvarchar](50) NULL,
[ProductImageName] [nvarchar](250) NULL ,
[ProductSmallImageName] [nvarchar](250) NULL ,
[ProductXImageName] [nvarchar](250) NULL ,
[PutIn] [nvarchar](250) NULL,
[RowId] [int] NULL,
[IsEnabled] [bit] NULL ,
[IsDeleted] [bit] NULL ,
[DateSubmitted] [datetime] NOT NULL ,
[DateUpdated] [datetime] NOT NULL ,
[RowIndex] int
)

Insert into #TempTBL
(
[ProductCategoryId] ,
[ProductSubCategoryId],
[ProductId],
[ProductName] ,
[ProductSupplier],
[ProductShortDescription],
[ProductModel],
[ProductLongDescription],
[ProductRRP],
[ProductNowRate],
[ProductMake],
[ProductType],
[ProductImageName],
[ProductSmallImageName],
[ProductXImageName],
[PutIn],
[RowId],
[IsEnabled],
[IsDeleted],
[DateSubmitted],
[DateUpdated],
[RowIndex]
)
select *,row_number() OVER(Order By ProductId)
from Products
where ProductCategoryId=@ProductCategoryId AND
ProductSubCategoryId = @ProductSubCategoryId AND
IsDeleted = @IsDeleted AND IsEnabled = @IsEnabled
ORDER By ProductId ASC

select * from #TempTBL where RowIndex >= @page * @count AND RowIndex <= (@page * @count) + @count
END
GO

Thanks a lot, that was a very helpful post.

I would add this bit:

n is the offset
x is the number you start on

e.g. Show pages 0 to 25 –
SELECT TOP 25 *
FROM tablename
WHERE key NOT IN (
SELECT TOP 0 key
FROM tablename
ORDER BY key
)

e.g. Show pages 100 to 150 –
SELECT TOP 150 *
FROM tablename
WHERE key NOT IN (
SELECT TOP 100 key
FROM tablename
ORDER BY key
)

So what is the answer for Oracle users? The best I have come up with to get the first row only is a subquery like:

SELECT firstname, lastname, score
FROM candidates
WHERE score =
( SELECT MAX(score) FROM candidates);

but this doesn’t allow for an offset and I can’t see how one can be set.

Leave a Reply

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