Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Equivalent to mysql LIMIT statement

15 views
Skip to first unread message

Jakub Cermak

unread,
Aug 22, 2006, 1:42:00 PM8/22/06
to
Hi all,
I'm looking for equivalent to LIMIT statement which is in mysql.
-quote-
The LIMIT clause can be used to constrain the number of rows returned by
the SELECT statement. LIMIT takes one or two numeric arguments, which
must both be non-negative integer constants (except when using prepared
statements).

With two arguments, the first argument specifies the offset of the first
row to return, and the second specifies the maximum number of rows to
return. The offset of the initial row is 0 (not 1)
-quote end-

Basicly I want to select (for example) 10 rows beginning from 5th row.

Can anyone help me?
--
Best regards,

Jakub Cermak
ICQ 159971304
http://cermi.wz.cz

Tracy McKibben

unread,
Aug 22, 2006, 1:48:30 PM8/22/06
to

TOP is the equivalent in SQL Server, but it doesn't support an offset.
You can obtain the same behavior with a combination of derived tables
and ascending/descending ORDER BY clauses, something like this:

SELECT col1, col2
FROM
(
SELECT TOP 10 col1, col2
FROM
(
SELECT TOP 15 col1, col2
FROM table
ORDER BY col1 ASC
) AS derivedtable1
ORDER BY col1 DESC
) AS derivedtable2
ORDER BY col1 ASC


--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Kalen Delaney

unread,
Aug 22, 2006, 1:58:08 PM8/22/06
to
Hi Jakub

Do you want a separate statement, or just a clause within a statement?

You can use
SET ROWCOUNT 10
to limit all statements to affecting no more than 10 rows, so a SELECT will
return no more than 10, an UPDATE will update no more than 10, etc. All
statements will be affected until you use
SET ROWCOUNT 0

You can more read about SET ROWCOUNT in the Books Online.

Within a statement, you can use the TOP clause. The following statement
returns no more than 10 rows:

SELECT TOP 10 *
FROM MyTable

Please read about the TOP clause in the Books Online.

There is no way to says 'starting from the 5th row' because there is no such
thing as the 5th row. In a relational database table, the rows are not
ordered unless you request that the results be ordered by using ORDER BY.

So if you have a particular column that determines what the order is, you
could try something like this, which would give you the bottom 10 of the top
14, which is the same as the top 10 starting with the 5th.

SELECT TOP 10 *
FROM (SELECT TOP 14 * FROM MyTable ORDER BY OrderColumn) as T
ORDER BY OrderColumn DESC

--
HTH
Kalen Delaney, SQL Server MVP


"Jakub Cermak" <ja.c...@centrum.cz> wrote in message
news:eNsXzIhx...@TK2MSFTNGP04.phx.gbl...

Anith Sen

unread,
Aug 22, 2006, 2:42:41 PM8/22/06
to
The general term for such queries is known as quota queries. The crux of the
issue is identifying the beginning of the quota, essentially identifying the
"5th row" in your case. So the general logic would be to sorts the rows
based on some explicit collating sequence of values in a column, identify a
specific subset.

In SQL 2005, you can use ROW_NUMBER() or RANK() functions & use them to get
the quota. In older versions, you might find some ideas here:
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html

--
Anith


Steve Dassin

unread,
Aug 23, 2006, 1:59:09 PM8/23/06
to
"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:%23EXqArh...@TK2MSFTNGP06.phx.gbl...

> The general term for such queries is known as quota queries.

You talk the talk but do you walk the walk? :( :)

http://racster.blogspot.com/


Anith Sen

unread,
Aug 25, 2006, 9:27:03 AM8/25/06
to
>> You talk the talk but do you walk the walk? :( :)
>>
>> http://racster.blogspot.com/

Still toddling. Btw, interesting blog, Steve. Glad to see D4 ( and realSQL
too? ) is alive and kicking.

--
Anith


Steve Dassin

unread,
Aug 25, 2006, 3:31:05 PM8/25/06
to
No, 'you' are not a toddler!
There are so few that get 'it'. Those that do can try
to do something about it.
'You' are being dumbed down by your own profession.
Doesn't that bother you??
Your profession needs 'you'. Find your way to show
another way.

best,
steve

"Anith Sen" <an...@bizdatasolutions.com> wrote in message

news:%23Gd1noE...@TK2MSFTNGP03.phx.gbl...

0 new messages