469,610 Members | 2,042 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

LIMIT in MS SQL ??!!

hi,

I have a question.
Maybe You know the equivalent to command LIMIT from MySQL
I couldn`t find something like this in MS SQL
PS
I try to display 10 records begining form e.g. 4 sort by id
something like: "SELECT * FROM table WHERE name=... LIMIT 4, 10 ORDER BY id"
in MySQL

thanx,
Urban
Jul 20 '05 #1
2 17807
Hello!

Please see the TOP command in your Books on-line... select top 10 * from
table ...

Cheers,
Dejan
"Urban" <kl******@tlen.pl> wrote in message
news:co**********@news.zigzag.pl...
| hi,
|
| I have a question.
| Maybe You know the equivalent to command LIMIT from MySQL
| I couldn`t find something like this in MS SQL
| PS
| I try to display 10 records begining form e.g. 4 sort by id
| something like: "SELECT * FROM table WHERE name=... LIMIT 4, 10 ORDER BY
id"
| in MySQL
|
| thanx,
| Urban
|
|
Jul 20 '05 #2
"Urban" <kl******@tlen.pl> wrote in message news:co**********@news.zigzag.pl...
hi,

I have a question.
Maybe You know the equivalent to command LIMIT from MySQL
I couldn`t find something like this in MS SQL
PS
I try to display 10 records begining form e.g. 4 sort by id
something like: "SELECT * FROM table WHERE name=... LIMIT 4, 10 ORDER BY id"
in MySQL

thanx,
Urban


Say you have table T with a column C whose values are returned
in the order given by <column order> and, given this order, you'd
like N rows starting from row S. The MySQL query is:

SELECT C
FROM T
ORDER BY C <column order>
LIMIT S, N

Note that the initial row with the LIMIT clause is 0, not 1.

In T-SQL, one can write the following using the product-specific
TOP clause:

SELECT TOP N C
FROM (SELECT TOP S + N C
FROM T
ORDER BY C <column order>) AS TopN(C)
ORDER BY C <opposite column order>

For example, the MySQL code

SELECT C
FROM T
ORDER BY C DESC
LIMIT 5, 10

orders column C in descending order and returns 10 rows
from row 6 to 15. This can be can be written in T-SQL by
plugging into the above to get

SELECT TOP 10 C
FROM (SELECT TOP 15 C
FROM T
ORDER BY C DESC) AS TopN(C)
ORDER BY C ASC

Obviously, the one-argument version of LIMIT, e.g.,

SELECT C
FROM T
ORDER BY C <column order>
LIMIT N

is simply the following in T-SQL:

SELECT TOP N C
FROM T
ORDER BY C <column order>

--
JAG
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Afkamm | last post: by
3 posts views Thread by CajunCoiler \(http://www.cajuncoiler.tk\) | last post: by
3 posts views Thread by Jay K | last post: by
4 posts views Thread by emily_g107 | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.