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