Re: Top ten?

Michael Collier (sd345_at_city.ac.uk) wrote:
: Sorry if this is a FAQ.
:
: Suppose I am doing a group by query which returns lots of rows but I want the
: top ten (say).
:
: ie. select something, count(*)
: from a_table
: group by something
: order by something;
:
: I'd like the top ten something's, how do I do this?

Something like this should work (I'll leave the testing to you :-)

SELECT DISTINCT a.something
FROM a_table a
WHERE 10 < (SELECT COUNT(*)

	    FROM a_table b
	    WHERE a.something < b.something)

:
: Thanks in advance. Michael.

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Jan Diesel                                       |          jwd_at_rivm.nl
Informatics Service Centre                       |  tel. ++31 30 742067
P.O.Box 1, 3720 BA  BILTHOVEN, the Netherlands   |  fax. ++31 30 282316
RIVM - National Institute of Public Health and Environmental Protection