Jump to content


Photo

Is there a way to limit number of records per join?


  • Please log in to reply
2 replies to this topic

#1 aumbrother

aumbrother
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 22 August 2006 - 02:03 PM

Here is a tough one for you.

Say, I have a table "categories" and a table "topics".
Each category has thousands of topics in it.
I want to write a join query that would display all categories with 10 top rated titles next to it.

Here is what I have so far:

SELECT c.title,t.title,COUNT(t.id) AS topic_count
FROM category AS c
LEFT JOIN ON t.category_id = c.id
GROUP BY c.id
ORDER BY topic_count DESC


How do I modify this query so that it only gives me up to 10 topics per category?

Thanks in advance



#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 August 2006 - 04:54 PM

You'd probably need to JOIN in a dervied table.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 aumbrother

aumbrother
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 23 August 2006 - 03:51 PM

Thanks, that works




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users