CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Kohjah Breese
Kohjah Breese

Reputation: 4136

N-per-group with JOIN

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

Upvotes: 6

Views: 309

Answers (2)

Shlomi Noach
Shlomi Noach

Reputation: 9364

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.

Upvotes: 0

Kyra
Kyra

Reputation: 5407

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 ...

Upvotes: 2

Related Questions