Top ten again?
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