Re: Tricky SQL question

From: Jurij Modic <jmodic_at_src.si>
Date: Wed, 06 Jan 1999 12:21:55 GMT
Message-ID: <3693529a.4334340@news.siol.net>

On Tue, 05 Jan 1999 21:28:15 GMT, jmodic_at_src.si (Jurij Modic) wrote:

>SELECT v1.keyword FROM
> (SELECT max(v2.keyword) data_type, v2.cnt FROM
> (SELECT keyword, -1*COUNT(*) cnt FROM keyword
> GROUP by keyword
> ) v2
> GROUP BY cnt
> ) v1
>WHERE rownum <= 10;

Sory, the above query has one bug. If there are some keywords among top-10 that have the same number of occurances, the above query will return only one of them!

Here is the corrected version (and even more ugly, though):

SELECT v1.keyword FROM
 (SELECT MAX(v2.keyword) keyword,

    -1*TO_NUMBER(LPAD(v2.cnt,10,'0')||LPAD(rownum,10,'0')) FROM     (SELECT keyword, COUNT(*) cnt FROM keyword      GROUP BY keyword
    ) v2
   GROUP BY -1*TO_NUMBER(LPAD(v2.cnt,10,'0')||LPAD(rownum,10,'0'))   ) v1
WHERE rownum <=10;

Regards,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Wed Jan 06 1999 - 06:21:55 CST