First 3 within a group?

[jmabbate]

Hi,

 

I\'ve got a table with many different items which are categorized into eight groups. An auxiliary table has counts of hits against each item. I\'d like to select the top 3 items in each group, i.e., a total of 24 rows. I know I can do this in a loop, e.g., (in pseudo-php-mysql)

 

    for ($i = 1; $i <= 8; ++$i)

          select item, hitcount from hits

               where group = $i

               order by hits desc limit 0, 3;

 

I was wondering whether there was some way to do this in a single SQL invocation instead of eight passes. I\'m sort of familiar with the GROUP BY and HAVING clauses, but I\'m not sure they can be used to achieve this.

 

Any ideas?

 

Joe

[gizmola]

Not really. You could use a group by group & item to get the sum(hits). This would give you everything you needed in one query, but you would still have to go through it and discard the items in each group which were not the top 3 in each group. This *might* be more efficient than what you are doing (a seperate query for each group) assuming you have a limited number of total items.

 

The query would be something like:

 

select a.group, a.item, sum(hitcount) as hitcount

from item a, hits b

where b.item = a.item

group by a.group, a.item

order by a.group, hitcount

[jmabbate]

I think I didn\'t explain myself properly. Maybe an example will help. The data looks like this:

 

Grp   Item   Count

 1    abc      32       *

 1    def      17       *

 1    pqr      12       *

 1    lmo       7

 1    xyz       1

 2    igh      12       *

 2    lkj       9       *

 2    uts       5       *

 2    xwv       3

 

What I want is a query that will only display the rows that have an asterisk. In other words, group rows by grp number, ordering each row by descending count and pick only the top 3 rows in each group. Also, if a group has less than 3 rows at the top, I only want to show the first two (or one, whatever that is). The \"top 3\" could end up being \"top 5\" or something else.

 

I could use a single query as you suggested, e.g., \"select grp, item, count order by grp, count desc\", but I think eventually that would become inefficient since the table will continue to grow. Also, my example is simplified: the real problem has two tables, so a join is necessary to get the grp and item (name) [join on item number], plus some rows are discarded based on columns in the first table, and the counts table also has a timestamp which perhaps will also be used to restrict rows.

 

Joe

[gizmola]

I did understand you, and I believe I addressed your question. There is no magic bullet that I know of with pure mysql SQL for this. If there is a way, it would have to involve some sort of computational column, but I doubt there\'s anything that will get around the group by issue.

 

So you\'re really down to deciding whether one query with the group by works best, or seperate queries for each group does.

 

*If* there was some sort of magic way to handle this, it would have to involve a computational trick involving a mysql function, but I couldn\'t thing of any that even warranted an experiment. Typically this would be handled easily (using a cursor) if Mysql offered stored procedures, but it does not.

 

As to which of your two choices makes sense, as you stated, if you can limit the single result set down to a manageable number of rows my gut instinct is that will be faster than doing a whole series of seperate queries.

 

If I come up with any brainstorms I\'ll let you know (digs for old copy of SQL for smartys)