"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