Results 1 to 5 of 5

Thread: MySQL LIMIT equivalent in MS SQL 2000

  1. #1
    Join Date
    Oct 2007
    Posts
    3

    MySQL LIMIT equivalent in MS SQL 2000

    I am looking for the MS SQL equivalent of MySQL LIMIT.

    I know that the code in MySQL is

    SELECT col_name FROM tbl_name LIMIT x,y

    where x = the starting row and y = the offset

    E.g. SELECT movie_name FROM my_movies LIMIT 10,5

    This will give me the records from 11-15

    I would like to know what is the MS SQL equivalent for the above?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Don't have that in sql server. If the table has id column, you can use that column in where clause.

  3. #3
    Join Date
    Oct 2007
    Posts
    3
    Since I got no reply, I have been able to figure out a query on how to do that in MS SQL.

    Say you want to do the following in mySQL
    LIMIT x,y

    The same can be accomplished in MS SQL using the query that follows:

    SELECT TOP y * FROM tbl_name
    WHERE col_id NOT IN
    (SELECT TOP x col_id FROM tbl_name)

    Say you want Limit 4,10
    What this does in mySQL is that it fetches the rows from 5-14

    So what you are doing in the above MS SQL query is actually selecting the first 4 rows, and then select everything except those 4 rows.

    Of course you use the TOP 10 in the outside query to get the offset you want.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    You may need order by clause, otherwsie the result is not guaranteed.

  5. #5
    Join Date
    Oct 2007
    Posts
    3
    Ya you do need the ORDER BY clause, and the code is as follows:
    SELECT TOP y * FROM tbl_name
    WHERE col_id NOT IN
    (SELECT TOP x col_id FROM tbl_name ORDER BY col_id DESC) ORDER BY col_id DESC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •