Re: SQL select first 10 or so entries from a table

From: Ed prochak <ed.prochak_at_alltel.com>
Date: 16 Apr 2002 13:55:47 -0700
Message-ID: <e51b160.0204161255.68c7ab49_at_posting.google.com>


bbulsara23_at_hotmail.com (barry) wrote in message news:<747f1dec.0204140251.54ee7eb0_at_posting.google.com>...
> Hello, imagine I have lots of tables each containing 30-75,000
> entries.
>
> The query
>
> SELECT * FROM tablename WHERE something=somethingelse
>
> returns on occasions many thousand hits.
>
> What sort of SQL construct do you use to say give me the first 5
> entries.
> Then what SQL do you use to get the next 5 entries.
> Then the SQL to get the 5 after that etc, for a table in a general
> sense. I don't care about the 'order' of the results that come back, I
> just want to display things in bite size chunks (like a hitset, and if
> the user doesn't find the result they're after in the client
> front-end, they hit the "next" button).
>
> Again thank you in advance to everyone that thinks about, or posts,
> solutions to this query.
> Barry

To do precisely what you described, use the PAUSE option in SQLPLUS. There is no change to your query, just

SET PAUSE ON just before your query. enter your query as normal. One quirk is that no results are displayed unless you hit the RETURN at least once. Each time you hit return, it will show the next "page" of results, including header. Of course you can use the SET PAGESIZE and SET LINESIZE and other sql features to pretty up the display.

Quite honestly I have never found the "WHERE ROWS < " feature to be of any use to me. If I'm displaying for myself in SQLPLUS, I use PAUSE. If I'm displaying results in an application, I control the FETCHes in the language I'm using (Pro*C, PL/SQL, FORMS, PERL, whatever). Similarly with DISTINCT, I seldom find a use for it.

HTH,
  Ed
(contracting to, but not speaking for ALLTEL)

Edward J. Prochak --- Magic Interface, Ltd. Ofc: 440-498-3700 Cell: 440-666-9013
on the web at --- http://www.magicinterface.com email: ed.prochak_at_magicinterface.com Received on Tue Apr 16 2002 - 22:55:47 CEST

Original text of this message