Re: How can I get the first 20 records !!

Same thing, but more confusing. And you can certainly use it to narrow the result set down - it has few other practical uses!

ETA markyg_7_at_yahoo.co.uk (SoulSurvivor) wrote in message news:<8d9c6fd.0303200227.36ab08c6_at_posting.google.com>...
> Age old problem, age old mistake in initial solution!!
>
> Depending on what you mean by 'first 20 records', using the rownum <
> 20 will NOT work. Rownum is a pseudo column and is worked out after
> the result set is parsed. You cant use it in the where clause to
> narrow down.
>
> Looking at your SQL, what you really want to do is something like the
> following.
>
> SELECT a.name, a.address FROM
> (select rownum cnt, name, postcode
> from address where lastname >= ' ' and firstname > ' '
> order by lastname, firstname)
> WHERE a.cnt < 20;
>
> The above *should* work depending on your Oracle version.
>
> M
>
> Miko_at_boehrer.de (Ralf) wrote in message news:<fa624200.0303190707.2652ad4c_at_posting.google.com>...
> > Hi to all,
> >
> > I have a very simple question and hope there is an answer.
> > I have a huge table: "address" (about 1.000.000 records):
> > (colums: ID, lastname, firstname)
> > (indices: idx_id (=unique), idx_lastname_firstname)
> >
> > The only thing I want is to get the first 20 records in
> > the order: "lastname-firstname"
> > in a very short time.
> >
> > I know some approaches which don't work :
> >
> > 1. select /*+ index (address idx_lastname_firstname) */
> > * from address where lastname >= ' ' and firstname > ' ' and rownum <= 20;
> > (This doesn't work properly, because the index-hint is only a hint and it's
> > not guaranteed that, if I would have a similar index (e.g.
> > idx_firstname_lastname) my supposed index is used).
> >
> > 2. select * from
> > ( select * from address where lastname >= ' ' order by lastname,firstname)
> > where rownum <= 20;
> > (This does work properly, but it takes to much time to get the results
> > because the inner select-statement creates a big cursor. This is the
> > reason why I can't use this type of select-statement).
> >
> > I have no more further ideas to implement my request.
> > Please response to me if you have any idea or comment to my problem.
> >
> > Thanks in advance.
> > Ralf
Received on Thu Mar 20 2003 - 15:27:11 CET