Re: Query regarding SQL Query - Limiting Result Set

You can write the following query:

SELECT * FROM
    (select * from TABLE_NAME
    WHERE NAME = 'someperson'
    ORDER BY RECORD_DATE desc)
where rownum<=5;

Regards,
Paul

"Dave Ortman" <dortman_at_my-deja.com> wrote in message news:8au85k$4gj$1_at_nnrp1.deja.com...
> Hi,
> I'm starting with the following query:
> SELECT * FROM TABLE_NAME WHERE NAME = 'someperson' ORDER BY RECORD_DATE
> DESC
> Essentially, I want to return the last five records by date for a
> certain individual. I attempted using rownum as follows:
> SELECT name, rownum FROM TABLE_NAME WHERE NAME = 'someperson' ORDER BY
> RECORD_DATE DESC
> However, Oracle derives the rownum value prior to executing the order
> by clause. I then attempted to utilize a subquery, like:
> SELECT name, rownum FROM (SELECT * FROMTABLE_NAME WHERE NAME =
> 'someperson' ORDER BY RECORD_DATE DESC)
> Unfortunately, one is not allowed to perform an order by clause inside
> of a subquery.
> My desire is to return the latest X records for a certain individual.
> Can I accomplish this using SQL? Or must I simply grab all the records
> for a given person and extract the ones I want within my application
> logic?
> Thanks in advance for any advice.
> -Dave
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Mar 17 2000 - 22:21:04 CST