N-per-group with JOIN

Question

I'm trying to replicate the logic as show here. However, I'm not having any luck when there are joins. Below is a minimised version of my query:

SELECT resources.title, catRel.catRef
FROM resources 
LEFT JOIN placesRel ON placesRel.refId = resId 
LEFT JOIN catRel ON refId = resId
WHERE ...

In short, I'm getting a list, which contains category Ids and I want to limit the results to have no more than n results from a category, for example, show only two results per catRef:

title             catRef
Swizz Gubbinz     1
Runcible Spoons   1
Peter Pan DVD     2
Button Moon       2
Monkey Alan       3
Bilge Pump        3

I've updated the thread to be a little more specific, as the output example I give suggested I was looking for catRef = 2. title is in resources and catRef is in catRel. — Kohjah Breese, May 22 '12 at 21:36
You are using mysql. This is easy using the row_number() function, which mysql does not (yet?) support. If you only want two values, I might suggest doing a group by, and doing the min() and max() on a single row. — Gordon Linoff, May 22 '12 at 21:56
[Here is a good article about mimicking row_number() functionality in mySql](http://www.explodybits.com/2011/11/mysql-row-number/). Once you have a row_number() per group you can simply filter records with rn <= 2 or whatever you need at the moment. — Nikola Markovinović, May 22 '12 at 22:07
Thank you all for your time nd advice. I'm not on the computer now, but it sounds like one or all of the suggested solutions will work. — Kohjah Breese, May 22 '12 at 22:22
I had a similar problem a while back and the first answer here worked well http://stackoverflow.com/questions/9969126/mysql-select-top-x-records-for-each-individual-in-table — frostmatthew, May 22 '12 at 22:44
Having looked into it a bit further the solutions provided by Nikola and Exupery only work when there are no join. Otherwise you get something like this: title catRef rowNumber Item1 1 1 Item2 1 2 Item3 1 3 Item4 2 1 Item5 1 1 i.e. it gets processed as it comes out of the DB, rather than being ordered on the column you want to group by. Investigating.... — Kohjah Breese, May 23 '12 at 01:02

score 2 · Answer 1 · answered May 22 '12 at 22:11

2

How about using an subquery within your join. I wasn't sure which table refID and resID belonged to but.....

SELECT resources.title, catRel.catRef
FROM resources 
LEFT JOIN placesRel ON placesRel.refId = resId 
LEFT JOIN catRel as cr1 ON cr1.catRel.primaryKey in (select cr2.primaryKey from catRel as cr2 where cr2.refID = resId Limit 0,2)
WHERE ...

answered May 22 '12 at 22:11

Kyra

5,129
5
35
55

1

haven't tested this- but it's what I'd try, maybe with an "order by" clause in the subquery – ErichBSchulz Jul 07 '12 at 06:11

score 0 · Answer 2 · answered Jul 06 '12 at 17:03

In the lack of Window Functions from MySQL, the answer is not trivial. Here's a trick which selects top N record per group, by utilizing MySQL's GROUP_CONCAT: MySQL: Selecting Top N Records Per Group.

Being an aggregate function, GROUP_CONCAT can be manipulated to provide with concatenated strings in desired order. Using text manipulation, the string is parsed. Optionally, values are cast to proper types.

N-per-group with JOIN

2 Answers2