Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Query regarding SQL Query - Limiting Result Set
In article <8au85k$4gj$1_at_nnrp1.deja.com>,
Dave Ortman <dortman_at_my-deja.com> wrote:
> 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
This is the very old question. Very old answer:
select name
from table_name a
where name = 'someperson'
and 5 < (select count(*) from table_name b
and name = 'someperson' and b.record_date < a.record_date)
I might've used wrong comparison operators, but that's an idea.
> 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.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sat Mar 18 2000 - 23:26:48 CST
![]() |
![]() |