Top ten again?

From: Michael Collier <sd345_at_city.ac.uk>
Date: 20 Apr 93 07:58:17 GMT
Message-ID: <sd345.735292697_at_Hampton>


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.
Received on Tue Apr 20 1993 - 09:58:17 CEST

Original text of this message