Top 3 Occurances? 2

[Yrrk]

Lets say i had a table with two fields..

project_name varchar(20)
error_description varchar(20)

and it was full of entries for various projects.. How would I determine what the top 3 occurances for error_description are for each project and their occurance count?

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

select project_name,error_description,count(error_description)
from tablename
group by error_description;

But how to get only the top 3?

I'm using mysql 4.0.x which does not support sub-queries. I'm thinking i need to create a temporary table but can't seem to get it right.

need help..

 

[Bastien]

add

order by project_name desc limit 3

Bastien

Cat, the other other white meat

 

[r937]

no, Yrrk wants top 3 per project

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)

 

[Yrrk]

r937, thanks i'm not really sure how to do that. i've used temp tables before for similar things but for some reason can't come up with a working varient in this case. Could you show me what you're thinking?

 

[r937]

create temporary table projectcounts
select project_name
     , error_description
     , count(*)    as errors
  from tablename
group 
    by project_name
     , error_description
;    
select t1.project_name
     , t1.error_description
     , t1.errors
  from tablename t1
inner
  join tablename t2
    on t1.project_name = t2.project_name
   and t1.errors <= t2.errors     
group 
    by t1.project_name
     , t1.error_description
     , t1.errors
having count(*) <= 3
order 
    by t1.project_name
     , t1,error_description
     , t1.errors  desc

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

 

[r937]

oops, in the last SELECT, the table name for t1 and t2 should be the name of the temporary table, projectcounts

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

 

[Yrrk]

Thats a nice piece of work No wonder I couldn't come up with a working varient. I get an error when I try the above though for the second select statement..

ERROR 1137 at line 38: Can't reopen table: 't1'

line 38 is the line of my select statement (your lower select statement).

 

[r937]

?? could you please show the query?

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

 

[Yrrk]

ahhh i got it. I just created two temporary tables and just called them seperate names (projectcounts1, projectcounts2) and made appropriate changes below and it worked.

Thanks very much thats great. Helps me see SQL queries in a new light too. I haven't used inner joins before.

 

[r937]

ok, now that sort rings a very faint, remote bell

i think you cannot self-join a temp table

your creation of two temp tables is a clever workaround

would've taken me a long time to think of it

star for you too



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

 

[Yrrk]

This is the final results. Notice the descending ordering doesn't seem to be working in all cases. Not sure why that is.

create temporary table ErrorCount
  select RT.ProjectName,EHT.Description,count(EHT.Description) as NumberOfOccurances
    from ProjectTable PT, RestoreTable RT, ErrorHistoryTable EHT
    where PT.Active='Active'
      and PT.ProjectName=RT.ProjectName
      and RT.RestoreID=EHT.RestoreID
      and (RT.ProcessingStatus LIKE "ReadyForDrainToDS"
        or RT.ProcessingStatus LIKE 'Completed')
      and RT.RestoreStatus NOT LIKE '%Errored%'
      and RT.RestoreStatus NOT LIKE '%Cancelled%'
    group by RT.ProjectName,EHT.Description;

create temporary table ErrorCount2
  select RT.ProjectName,EHT.Description,count(EHT.Description) as NumberOfOccurances
    from ProjectTable PT, RestoreTable RT, ErrorHistoryTable EHT
    where PT.Active='Active'
      and PT.ProjectName=RT.ProjectName
      and RT.RestoreID=EHT.RestoreID
      and (RT.ProcessingStatus LIKE "ReadyForDrainToDS"
        or RT.ProcessingStatus LIKE 'Completed')
      and RT.RestoreStatus NOT LIKE '%Errored%'
      and RT.RestoreStatus NOT LIKE '%Cancelled%'
    group by RT.ProjectName,EHT.Description;

select t1.ProjectName, SUBSTRING(t1.Description,1,50), t1.NumberOfOccurances
  from ErrorCount t1
    inner join ErrorCount2 t2
  on t1.ProjectName=t2.ProjectName
    and t1.NumberOfOccurances<=t2.NumberOfOccurances
  group by t1.ProjectName, t1.Description, t1.NumberOfOccurances
  having count(*) <= 3
  order by t1.ProjectName, t1.Description, t1.NumberOfOccurances desc;

drop temporary table ErrorCount;
drop temporary table ErrorCount2;

+----------------------+----------------------------------------------------+--------------------+
| ProjectName          | SUBSTRING(t1.Description,1,50)                     | NumberOfOccurances |
+----------------------+----------------------------------------------------+--------------------+
| A                    | Initialization failure                             |                 13 |
| A                    | Initialization failure.                            |                  4 |
| A                    | Received non-zero return code                      |                 33 |
| B                    | Completed                                          |               1615 |
| B                    | Mailbox(es) processing error.TAPEERROR: Processing |                252 |
| B                    | Received non-zero return code                      |                847 |
| C                    | Completed                                          |               2618 |
| C                    | ERROR-Activation                                   |                473 |
| C                    | Received non-zero return code                      |               2268 |
| D                    | Count validation error.TAPEERROR: Validate count o |                224 |
| D                    | Mailbox(es) processing error.TAPEERROR: Processing |                125 |
| D                    | Received non-zero return code                      |               2331 |
| E                    | Count validation error                             |                  3 |
| E                    | Initialization failure                             |                  7 |
| E                    | Received non-zero return code                      |                 14 |
| F                    | Initialization failure                             |                  7 |
| F                    | Received non-zero return code                      |                  4 |
| G                    | Initialization failure.                            |                200 |
| G                    | Mailbox(es) processing error.TAPEERROR: Processing |                148 |
| G                    | Received non-zero return code                      |                720 |
| H                    | Count validation error.TAPEERROR: Validate count o |                  1 |
| H                    | Processing stopped                                 |                  3 |
| H                    | Received non-zero return code                      |                 10 |
| I                    | Count validation error                             |                  1 |
| I                    | Initialization failure                             |                  8 |
| I                    | Received non-zero return code                      |                  9 |
| J                    | Initialization failure                             |                 28 |
| J                    | Mailbox(es) processing error                       |                 28 |
| J                    | Received non-zero return code                      |                 59 |
+----------------------+----------------------------------------------------+--------------------+

 

[r937]

i would definitely do the substringing when creating the temp tables, not when selecting from them

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

 

[Yrrk]

good idea.. I also dumbed down the second select statement to make it run twice as fast:

create temporary table ErrorCount2
  select *
  from ErrorCount;