-
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?
-
Don't have that in sql server. If the table has id column, you can use that column in where clause.
-
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.
-
You may need order by clause, otherwsie the result is not guaranteed.
-
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
-
Forum Rules
|
|