How to create fast database queries

Groups holding highest ranked items

Comments enabled. I *really* need your comment

Answering questions asked on the site.

Nate asks:

I know you've addressed similar issues related to the greatest-per-group query but this seems to be a different take on that.

Example table:

item_id group_id score
100 1 2
100 2 3
200 1 1
300 1 4
300 2 2

Each item may be in multiple groups. Each instance of an item in that group is given a score (how relevant it is the the group).

So given the data above, when querying for group 1 it should return items 200 and 300 (item 100's highest score is for group 2, so it's excluded).

The classical greatest-n-per-group problem requires selecting a single record from each group holding a group-wise maximum. This case is a little bit different: for a given group, we need to select all records holding an item-wise maximum.

Let's create a sample table:

Table creation details

Written by Quassnoi

April 22nd, 2010 at 11:00 pm

Posted in MySQL

Leave a Reply