By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,705 Members | 2,017 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,705 IT Pros & Developers. It's quick & easy.

LIMIT in MS SQL ??!!

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
"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.