MySQL's "LIMIT" and SQL Server

Anon

I'm trying to write a query to MS SQL Server which will return the first n rows found (let's say ten).

With MySQL, I would use something like "SELECT * FROM table ORDER BY id LIMIT 10"

With SQL Server, I get a syntax error with the same SQL statement, so there must be a different way to LIMIT the rows returned with SQL Server.

Anyone know what it is? I'd rather not have to write a 'for' loop if I can take care of the limiting within my SQL query. Thanks!

Anon

select * from table order by id limit 0,10

0 -> which record to start

10 -> show only 10 records

Kai

Anon

Modified query for SQL Server :-
SELECT top 10 * FROM table ORDER BY id

Anon

SELECT TOP works fine for returning a limited result set but it does not offer an offset option. To achieve an limit and offset that behaves like MySQL's hand LIMIT O, L parameters you will write a T-SQL stored procedure using a cursor. Here is the necessary code for the SP:

LimitSelect is a used for returning a query result set with a limit and an offset.

Syntax:
LimitSelect query, offset, limit

Example:
LimitSelect 'Select * From users', 10, 5
This call would return 5 rows starting from the 10th row

Notes:
For offsets, the first row of the set is zero.
*/

CREATE PROCEDURE LimitSelect
@query CHAR (128), -- SQL query, it'd better be a SELECT!
@ INT, -- limit the result set of the query
@offset INT -- start result set from offset
AS
-- Execute call to declare a global cursor (node_cursor) for the query passed to the SP
EXEC ('DECLARE node_cursor CURSOR GLOBAL SCROLL READ_ONLY FOR ' + @query)

-- open the global cursor declared above
OPEN node_cursor

-- tweak the starting values of limit and offset for use in the loop
SET @offset = @offset + 1
SET @limit = @limit - 1

-- advanced the cursor to the offset in the result set
FETCH ABSOLUTE @offset FROM node_cursor 

-- counter i
DECLARE @i INTEGER
SET  @i = 0

-- loop until limit reached by counter i
WHILE (@i < @limit)
BEGIN
	-- fetch the next row in the result set and advance counter i
	FETCH NEXT FROM node_cursor 
	SET @i = @i + 1
END

-- clean finish
CLOSE node_cursor
DEALLOCATE node_cursor

taxilian

Writing a stored procedure using a cursor, or possibly even better using a user defined function that could actually return the entire recordset (which would actually be much more efficient, as you could probably do most of it with set statements (insert into... select from), and you wouldn't have to allocate a cursor, which is costly to sql server performance.

Another way to do it, though it won't work in ALL situations, is briefly outlined in the article "What's after TOP?" on sqlteam.com. http://www.sqlteam.com/item.asp?ItemID=566

A few other ideas:

Find the id (or whatever you are sorting by) of the last item on the previous page, and then select top x from the table where that field > that value. this should return the next x rows in that field, at a very low server cost.

For other SQL server ideas, check out such sites as "sqlteam.com", "www.sql-server-performance.com", and "sqlmag.com". None of these have php specific help, but all have ideas that are valid in all languages, and some have ideas for asp pages that are also valid for PHP.

Hope this helps someone.

Anon

Got exactly this problem right now, and given the lack of info around online I actually reckon I'm thinking all wrong.

<ponders>Perhaps there is there some way to specify the page and cache size, like you do from ASP, with mysql_connect...</ponders>

In the meantime, here's how I'm doing it using a function and no cursors (hope the formatting works!):

CREATE FUNCTION LimitText ()
RETURNS @TestTable TABLE
(
RowNum INT IDENTITY (1,1) NOT NULL,
region_id INT,
title VARCHAR(50)
)
AS
BEGIN
INSERT @TestTable
SELECT region.region_id, title
FROM region
RETURN
END

SELECT region_id, title FROM LimitText() WHERE RowNum BETWEEN 10 AND 20

taxilian

This will work, but it will be extremely slow. One suggestion would be to modify your above function to accept 2 parameters: Start, and Length.

When you insert the rows into your function table, use top to select the first (startposition+length) records, then remove all records before startposition.

This should have the same effect, but end up being far faster, as the function doesn't have to deal with every row in the table -- every time. if you only want 200 rows, and it's in the middle of a 10000 row table, then it will still have to deal with everything up to the end of the 200 rows, but even if that was the majority of the table, it's still cutting out probably often several thousand rows. Even if you aren't doing this, the benefits are probably worth the extra trouble to write the logic.

anyway, just my $0.02, hope it helps.

Richard

Anon

Richard,

Many thanks for the advice. I'll follow it.

I've also found an article on 4GuysFromRomula that does basically the same thing in an SP. Even they don't use the TOP trick 🙂 But has a good bit on why you really do want to be doing this sort of thing, even if coding in <cough> ASP.

Link is:
http://www.4guysfromrolla.com/webtech/062899-1.shtml

Matt

zoul0813

I started a new thread with a different method of performing MySQL style LIMIT features ... its located here:

http://www.phpbuilder.com/forum/read.php3?num=5&id=32279&loc=0&thread=32279

netbros

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10211438