CODEWITHSUNDEEP
mysqlgroupinggreatest-n-per-group
jwegner
jwegner

Reputation: 7423

Selecting Greatest N records in X groups

So I've been running through all the questions under the greatest-n-per-group tag, and either I don't understand what I'm reading, or nothing has fit my needs so far. This link has also provided a lot of useful information, but still no answer.

So I've got a table with the following fields:

I believe my problem strays from the common greatest-n-per-group question, in that I don't need the greatest N for every group. I need the greatest N records for X groups.

So, I need to get X categories with the highest interest (simple, GROUP BY category ORDER BY interest, LIMIT X). And then I need to get N records with the highest score for each of those categories.

How would my query look for something like this?

Sorry if this is really a duplicate of every other greatest-n-per-group question, and I just don't understand how they work. Feel free to close this if so.

Update:

Per @tehshrike's prodding, here's a bit more information.

Really what's happening is that my user's have interest in lots of different categories. I would like to generate a list of users with the highest score in the categories that the original user is interested in.

So one possible thing that I'm looing for is:

The 15 users with the highest score in the 4 categories that user 1 is most interested in.

For a specific user_id find Top X categories (with highest interest for that specific user) and for those (X categories) find Top N users (with highest score for those categories).

Upvotes: 1

Views: 247

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115650

SELECT catX.category
       catX.interest
       t1.user_id
       t1.score
FROM 
    ( SELECT category 
           , interest 
      FROM tableX 
      WHERE user_id = @user_id_we_are_interested_in     --- specific user 
      ORDER BY interest DESC
      LIMIT @X                         --- top @X categories per specific user 
    ) AS catX 
  JOIN 
    tableX AS t1 
      ON t1.category = catX.category 
  LEFT JOIN 
    tableX AS t2 
      ON  t2.category = t1.category 
      AND t2.score > t1.score 
  GROUP BY t1.category
         , t1.user_id
  HAVING COUNT(t2.score) < @N                      --- top @N users per category 
  ORDER BY catX.interest DESC 
         , t1.score DESC

Upvotes: 1

TehShrike
TehShrike

Reputation: 10084

You probably want to write your groupwise-max query, and then limit it down by JOINing on something like this:

JOIN
(
  SELECT category
  FROM your_table
  ORDER BY interest
  LIMIT 10
) AS just_get_these_categories ON just_get_these_categories.category = your_table.category

(This is assuming that category is a candidate key in your table)

Edit: based on further questions, it looks like what you want is not possible. (See: chatlog)

Upvotes: 0

Related Questions