Helpful Information

Category: MS SQL Development

limit equivalent

Hi there :)

I am searching for an equivalent to MySQL/PostgreSQLs "LIMIT" for MS SQL Server. Any ideas?

TOP as in:
SELECT TOP 50 fieldname1, fieldname2 FROM table

Note that there's no equivalent in MS SQL for the two argument LIMIT clause, i.e. something like LIMIT 50, 10 to show 10 rows from the 50th row onwards. You cannot do this in MS SQL.

Actually that second one was the one I was looking for, and I found kind of a work-around ;) I knew of the TOP, but for creating a page with a limited amount of entries the LIMIT 0,10 would be best - but this "kind of" works ;)

SELECT * FROM (
SELECT TOP x * FROM (
SELECT TOP x * FROM (
SELECT TOP (x+y) columns
FROM tablename
ORDER BY key ASC
) AS foo ORDER BY key DESC
) AS bar
) AS baz ORDER BY key ASC

I found it here, if somebody is interessted:

http://216.239.39.104/search?q=cache:_5uXZDjGfGcJ:troels.arvin.dk/db/rdbms/+postgreSQL+limit+offset+Microsoft+SQL&hl=en&ie=UTF-8

Can one use a variable when using SELECT TOP?

That is:

SELECT top [ENTER NUMBER] DDAList_Main.IDNum, DDAList_Main.RAE_Assignment
FROM DDAList_Main
WHERE (((DDAList_Main.RAE_Assignment) Is Null));

rather than:

SELECT top 10 DDAList_Main.IDNum, DDAList_Main.RAE_Assignment
FROM DDAList_Main
WHERE (((DDAList_Main.RAE_Assignment) Is Null));

I think this is slightly more efficient (for the LIMIT thing)

SELECT * FROM(
SELECT
ROW_NUMBER() OVER (ORDER BY id ASC ) AS row
, id
FROM SQL_TABLE
) AS tbl
WHERE row > x
AND row < y
ORDER BY id

RE passing a variable to TOP

You'd need to dynamically construct the SQL in the stored procedure and then execute it. Not nice.

starting in SQL2005, TOP accepts a parameter

see http://www.mssqltips.com/tip.asp?tip=1535 for an example

remember that the number has to be in parentheses