I have the following SQL statement:
SELECT [id], [name]
FROM [dbo.test_db_002] t1
LEFT JOIN [dbo.test_db_003] t2 ON t1.[id] = t2.[itmid]
ORDER BY t2.[iid] ASC;
This seems very simple, but I can't figure it out. I need to add LIMIT N,M
to it to retrieve M items from the N'th one, but I keep getting errors around 'limit' word. I tried putting that LIMIT
clause everywhere I could inside the sql statement above with no avail.
PS. I'm writing for SQL Server that comes with VS2010.
To answer for your query, you may want: (depending on your values for M
and N
)
WITH cte AS
(
SELECT [id], [name], ROW_NUMBER() OVER (ORDER BY t2.[iid] ASC) AS rowNumber
FROM [dbo.test_db_002] t1
LEFT JOIN [dbo.test_db_003] t2 ON t1.[id] = t2.[itmid]
)
SELECT [id], [name]
FROM cte
WHERE rowNumber BETWEEN 3 AND 5
Something to watch out for, the values in the between are BETWEEN N AND N + M
Also, here's a link with information about Common Table Expressions which is the WITH cte
syntax I used.
There's no direct equivalent to LIMIT N,M
in SQL Server, but you can do something like this:
SELECT * FROM
(SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM MyTable) a
WHERE row > 5 and row <= 10
See here for some more info: "LIMIT" in SQL Server
You could use Row_Number()
example:
select * from
(
select cola, colb, row_number() over (order by col1 desc) as row
from table ) x
where row between value1 and value2
Limit with offset in sql server 2012:
SELECT email FROM myTable
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
//offset - no. of skipped rows
//next - required no. of next rows