Re: Specifying the maximum number of rows retrieved from a query?

Jim Poland wrote:
>
> Shannon J. O'Brien wrote:
> > Try something like:
> >
> > SELECT A.COLX FROM DATABASE.TABLENAME A
> > WHERE A.DATE > TO_DATE('DD-MON-YY')
> > AND ROWNUM < 3;
> >
> > make sure 'DD-MON-YY' is two days ago.
> > You could also be a little more general and use a where
> > clause like WHERE A.DATE > (SYSDATE - 2), I guess it depends
> > on you application....
>
> Since you want the two latest articles, you could also try an Order By
> clause instead of the A.DATE > TO_DATE('DD-MON-YY') portion of the Where
> clause.
>
> Jim

        Sorry 'bout that, I didn't read the Original Question correctly. (For some reason I thought she wanted 2 records over the last 2 days.) Anyhow, the order by clause you mentioned sounds good, but it is applied after the rows are returned. so if you tried something like this:

	SELECT A.COLX FROM DATABASE.TABLENAME A
	WHERE ROWNUM < 3
	ORDER BY A.DATE desc

You'd get the first 2 entries (most recent to the table) in cronological order w.r.t A.DATE

That sounds good for what Judy wanted, but it's not a perfect solution because how can you be sure that the most recent article added to a  table is actually the latest article. Something to keep in mind...

-Shannon.

-- 
	Shannon J. O'Brien		mailto:shannon_at_compusult.nf.ca
	Programmer/Analyst		http://www.compusult.nf.ca
	Compusult Ltd. Mount Pearl, NF	(Developers of CIDAS)