Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Re: Querying Select Number of Rows

by Arguile (Hermit)
on Jun 05, 2001 at 03:52 UTC ( #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.


Comment on Re: Querying Select Number of Rows
Select or Download Code

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (8)
As of 2015-07-04 19:17 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (60 votes), past polls