how to query top 3 occurances?

[Yrrk]

If i had a table with a varchar which was being used as a description of some error, and i wanted to know which error descriptions occured the most often, how would you go about query the top 3 description occurances and how many times the occured?

e.g. i could get a list of # of occurance of ALL errors through something like this:

select description,count(description)
from tablename
group by description;

But how to get only the top 3?

I'm using mysql 4.0.x

 

[Golom]

select TOP 3 description,count(description)
from tablename
group by description
ORDER BY 2 DESC;

 

[Yrrk]

hmm don't think mysql supports "TOP 3

 

[Yrrk]

think i got it..

select description,count(description)
from tablename
group by description
order by 2 desc
limit 3;

 

[Yrrk]

if i introduce an additional column it breaks again though..

select project,description,count(description)
from tablename
group by project,description
order by 3 desc
limit 3;

i only get the top 3 overall. What i'd like is the top 3 per project.

any thoughts?

 

[PHV]

You may try something like this:
select A.project,A.description,count(A.description)
from tablename A
where A.description in (select C.description from (
select B.description,count(B.description)
from tablename B where B.project=A.project
group by B.description
order by 2 desc limit 3
) C)
group by A.project,A.description;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[EtGvP]

My thoughts;

select project,description,count(description)
from tablename
group by project,description
order by 2 asc, 3 desc
limit 3;

/*
i only get the top 3 overall. What i'd like is the top 3 per project.

any thoughts?
*/

 

[Yrrk]

Sub-queries aren't supported in MySQL 4.0.x, only 4.1.x and up. So, PHV's doesn't help..

The second one doesn't seem to work either. I'm starting to think i can only do this by creating a temporary table..

 

[PHV]

So, PHV's doesn't help
You posted in the ANSI SQL forum, so you get ANSI SQL replies.

 

[r937]

You posted in the ANSI SQL forum, so you get ANSI SQL replies.

bravo

Yrrk, i can do "top N for each X" queries without subqueries using a self-join, however, it involves a grouping, and since your problem already involves grouping to obtain the N values...

you'll have to store your project description counts into a temp table, and then proceed from there

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)