Re: SQL - Selecting top n rows

In solution #3, does the '-1*' multiple the emp.sal by -1 so that the order will be reserved? If so, this works good for number fields, how would you do this for character fields?

Thanks,
Gary Bailey

P.S. The messages in this thread have been a huge help to me.

In article <740b11$p9i$2_at_news00.btx.dtag.de>,   GreMa_at_t-online.de (Matthias Gresz) wrote:
> John Wells schrieb:
> >
> > Help! I've got limited SQL knowledge, and need to get the top n rows of a
> > query. In MS Access (bleargh!) it's easy:
> >
> > SELECT TOP n name, age
> > FROM person
> > ORDER BY age DESC;
> >
> > But from what I can see, Oracle has no 'TOP' (non-standard?).
> >
>
> So, is there an easy way to do this on Oracle?
>
> Yes, take a look at Jurij Modic's solutions:
> (BTW, this question arrises often. Just take a look at dejanews news
> archive next time.)
>
> Couple of possible ways to do it...
>
> 1. (Caution - might be extremely slow on large tables)
> SELECT * FROM emp e1
> WHERE 1000 >= (SELECT COUNT(sal)
> FROM emp e2
> WHERE e2.sal >= e1.sal)
> ORDER BY e1.sal DESC;
>
> 2. (Much faster, for Oracle7.2 and higher)
> SELECT emp.* FROM emp,
> (SELECT rowid x, -1*sal FROM emp
> GROUP BY -1*sal, rowid) e2
> WHERE emp.rowid = e2.x
> AND rownum <= 1000
> ORDER BY emp.sal DESC;
>
> 3. (The fastest of all three)
> SELECT /*+ RULE */ emp.* FROM emp, dual
> WHERE -1*emp.sal = DECODE(dual.dummy(+),'X',NULL,NULL)
> AND rownum <= 1000
> ORDER BY emp.sal DESC;
>
> HTH
> Matthias
> --
> Matthias.Gresz_at_Privat.Post.DE
>
> Always log on the bright side of life.
> http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Dec 03 1998 - 10:27:45 CST