Re: Query regarding SQL Query - Limiting Result Set

From: Olga Greep <olga_at_hi.nl>
Date: Sun, 19 Mar 2000 00:46:02 +0100
Message-ID: <8b14h4$4fs3n$1@reader1.wxs.nl>

Hi Dave,

If you want the top X records for a certain column First find out if your column can contain equal values (for ‘someperson’) If not, then no problem,

If this can be the case decide what you want to see, Suppose you want the top 3 and number 3 and 4 equal, what do you want with number 4?
The following query would display number 4 as well

Select * from table_name t1
Where t1.name = ‘someperson’
And (X-1) >= (select table_name t2

                        Where t2.record_date > t1.record_date)

And t1.record_date is not null
Order by t1.record_date desc

I admit it looks a bit weird, but try it, it should work. If you feel the need, you can ask me to (try to) explain WHY it works I did not figure this out myself, I found simular questions in my course-book….

Good luck

Olga Greep

Dave Ortman wrote in message <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 Sat Mar 18 2000 - 17:46:02 CST