Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re: Querying Select Number of Rows

by Arguile (Hermit)
on Jun 05, 2001 at 03:52 UTC ( [id://85665]=note: print w/replies, xml ) Need Help??


in reply to Querying Select Number of Rows

As runrig said, syntaxes can vary... so here are some common ones:

PostgreSQL, MySQL, and some other have variants of the LIMIT x OFFSET y clause. This always (to my knowledge) appears as the last clause in the query.

... ORDER BY field1, field2 LIMIT 10 -- with OFFSET LIMIT 10 OFFSET 1 -- implicit OFFSET (MySQL) LIMIT 10,1

In Oracle you can use the "rownum" indexing of the recordset to return a specific number of records.

... FROM table1 a, table2 b WHERE rownum <= 10 ...

If you're working with an M$ DBMS like SQL Server (I refuse to allow Access to be called a DBMS) or one Informix's you can use the TOP syntax. TOP is part of the SELECT clause as seen below.

SELECT TOP 10 field1, field2, ... ,fieldn FROM ...

I know nothing of DBI so the next statement is a generality. The benefits to using the limiting at the DBMS side are smaller recordsets transfered back and often quicker queries (DBMS dependant), you do loose a certain amount of portability however. Staying with straight ANSI SQL and datatypes is always a problem though.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://85665]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others pondering the Monastery: (3)
As of 2025-06-19 19:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?
    erzuuliAnonymous Monks are no longer allowed to use Super Search, due to an excessive use of this resource by robots.