Re: Top ten again?

In article <sd345.735292697_at_Hampton>, sd345_at_city.ac.uk (Michael Collier) writes:
|> Thanks to all the people who responded to my earlier post by
|> by telling me to do a when rownum <= 10.
|> However this doesn't seem to work, I'll show the query I actually run below.
|>
|> select subject, count(*)
|> from adv_stats2
|> group by subject
|> order by count(*) desc;
|>
|> This gives me the following...
|>
|> SUBJECT COUNT(*)
|> --------------- ----------
|> WORDPERFECT 282
|> UNIX 253
|> DOS 86
|> TRIVIAL 79
|> PRINTING (UNIX) 71
|> ELM 52
|> PRINTING (DOS) 48
|> HECATE 47
|> WORDSTAR 46
|> QBASIC 38
|> C 32
|> DESKSCAN 30
|> AUTOCAD 29
|> HARVARD 29
|> QUATTRO PRO 27
|> ORACLE 22
|> WINDOWS 22
|> BMDP 20
|>
|> Now if I want the top ten, I've been told to use...
|>
|> select subject, count(*)
|> from adv_stats2
|> where rownum <= 10
|> group by subject
|> order by count(*) desc;
|>
|> ...which give...
|>
|> SUBJECT COUNT(*)
|> --------------- ----------
|> DOS 2
|> MONO 2
|> DBASE 3 1
|> OMNIPAGE 1
|> WORDSTAR 1
|> ORACLE 1
|> FORTRAN 1
|> FTP 1
|>
|> ...only 8 rows, but adding up the count(*) column gives 10.
|> So the where clause is only being applied to the first 10 rows of
|> the adv_stats2 table, instead of the the first 10 rows of the whole
|> query result.
|>
|> ? Michael.
|> --
|> Michael Collier (Programmer) The Computer Unit,
|> Email: M.P.Collier_at_uk.ac.city The City University,
|> Tel: 071 477-8000 x3769 London,
|> Fax: 071 477-8565 EC1V 0HB.

The following will select the people with the 10 top salaries from the scott.emp table

              select * from emp a where 10 >
              (select count(sal) from emp b
              where a.sal < b.sal)
              order by sal desc

NB this query returns 11 rows because two people have the 10th highest salary.

                       Ian Macgregor
                       Stanford Linear Accelerator Center
                       (415) 926-3528