SQL query with limit

Question

I have this table in SQL Server:

ID   | videoid  | title
=========================
1    | id1      | title1
2    | id2      | title2
3    | id3      | title3

And I want to create select method that search in the title row with:

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

And I'm looking for something like Limit in MySQL that from the SELECT results I will be able to get the 0-20,21-40,41-60 results.

Any help with this query?

I tried to use LIMIT 0, 10 and I received this error:

Could not find stored procedure 'LIMIT'.

Answer 1

You need to use TOP N with SQL SERVER.

SELECT TOP 10 * FROM [movie].[dbo].[movies] 
WHERE title like '%' + '%s' + '%'
ORDER BY SomeColumn -- Specify your column for ordering

See: TOP (Transact-SQL)

Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server

Also look under Best Practices in docs.

In a SELECT statement, always use an ORDER BY clause with the TOP clause. This is the only way to predictably indicate which rows are affected by TOP.

If you are looking for Paging records then you will need ROW_NUMBER

Answer 2

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.