SQL query with limit

In SQL Server 2012 a new feature was introduced that provides this functionality.

Look at the OFFSET part of the ORDER BY clause

SELECT *
FROM   your_table
ORDER
    BY some_column
       OFFSET 20 ROWS
       FETCH NEXT 10 ROWS ONLY

This will return the results 20-30 of your resultset (ordered by some_column)

For SQL Server 2005 - 2008R2 you can use windowed functions to perform the same action:

SELECT *
FROM   (
        SELECT *
             , Row_Number() OVER (ORDER BY some_column) As sequence
        FROM   your_table
       ) As a_subquery
WHERE  sequence >= 20
AND    sequence <= 30

For versions of SQL Server prior to SQL Server 2005 there is no efficient way of achieving this effect. Here's something that does the trick:

SELECT *
FROM   (
        SELECT *
             , (
                SELECT Count(*)
                FROM   your_table As x
                WHERE  x.some_column <= your_table.some_column
               ) As sequence
        FROM   your_table
       ) As a_subquery
WHERE  sequence >= 20
AND    sequence <= 30

Final notes: for your results to be deterministic some_column should be unique. If it isn't then you need to add extra column(s) in to the equation to provide a deterministic sort order for your sequence.

Also note that SELECT * ... should be avoided in all production code. Don't be lazy [like I was in this answer ;-)] - list out only the columns required.

There's no equivalent in T-SQL. TOP allows you to get only the first x results from the result set. You can use a trick. Using ROW_NUMBER you can add a new column that starts from 1 and is incremented automatically.

Like this:

SELECT ROW_NUMBER OVER (SomeExpression), Field FROM ...

Then you can use

SELECT TOP x FROM
(
    SELECT ROW_NUMBER OVER (ORDER BY ID) AS RowNumber, ID, videoid, title FROM ...
) tmp
WHERE RowNumber > Y ORDER BY RowNumber ASC

The trick is that this numbering is independent of the other fields, so using the same filter you'll always get the same RowNumbers and thus can filter again. This mimicks what LIMIT does.

For example, to get the entries 1 - 9 entries, you'd write:

SELECT TOP 9 FROM
(
    SELECT ROW_NUMBER OVER (ORDER BY ID) AS RowNumber, ID, videoid, title FROM ...
) tmp
WHERE RowNumber >= 1 ORDER BY RowNumber ASC

Next Page:

SELECT TOP 9 FROM
(
    SELECT ROW_NUMBER OVER (ORDER BY ID) AS RowNumber, ID, videoid, title FROM ...
) tmp
WHERE RowNumber >= 10 ORDER BY RowNumber ASC

Use Top:

SELECT TOP 1 * FROM [movie].[dbo].[movies] 
WHERE title like '%' + '%s' + '%'

When TOP is used in conjunction with the ORDER BY clause, the result set is limited to the first N number of ordered rows; otherwise, it returns the first N number of rows in an undefined order.

Read more here.

To get results like 10-20, use ROW_NUMBER:

SELECT TOP 10 * FROM 
 (SELECT ROW_NUMBER() OVER (id_field) as SlNo, ID, videoid, title 
  FROM TableName) T
WHERE SlNo>=10
ORDER BY id_field

I dont't think there's LIMIT in SQL server. Use TOP instead. TOP returns the first N rows of a query, so if it's TOP 10, even if you have a thousand rows, it will only return the first 10. Try this...

SELECT TOP 60 * 
FROM [movie].[dbo].[movies] 
WHERE title like '%' + '%s' + '%'

You can use following method for this purpose:

Method 1: ORDER BY

SELECT * 
FROM [movie].[dbo].[movies] 
WHERE title like '%' + '%s' + '%'
ORDER BY ID OFFSET 21 ROWS FETCH NEXT 20 ROWS ONLY

Method 2: ROW_NUMBER

SELECT *
FROM (  SELECT *,
                ROW_NUMBER()OVER (ORDER BY ID)row
        FROM [movie].[dbo].[movies]
        WHERE title like '%' + '%s' + '%'
    )z
WHERE row>20
    AND row<=40

Method 3: Top N

SELECT TOP 20 *
FROM (  SELECT TOP 40 *
        FROM [movie].[dbo].[movies] 
        WHERE title like '%' + '%s' + '%'
        ORDER BY ID
    )z
ORDER BY ID DESC