| re: LIMIT in MS SQL ??!!
"Urban" <klabinks@tlen.pl> wrote in message news:co76ls$lka$1@news.zigzag.pl...[color=blue]
> 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[/color]
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
| 
