Thanks to the genius of Simon, your friendly neighbourhood SQL man, I now have a solution to a problem that has bugged me for ages. Let me paint you a picture:
You have a list of categories in a database table. You have a list of items in another table, there are many items in each category. The categories and items are linked, the field “category_id” in the categories table is used in the “category_id” field in the items table.
So, what you want is to show a list of all the categories, a count of the number of items in that category and the name of the first X number of items in that category. And here’s what you’d do:
select c.category_name, i.item_name,
(
select count(item_id)
from items where category_id =
i.category_id
) as countitems
from items i
inner join category c on c.category_id = i.category_id
where (
select count(*) from items
where item_name >= i.item_name and category_id = i.category_id
)
order by c.category_name, i.item_name
Magic. You get exactly what you want, all in a single SQL query. And by changing the number (where it says “2”) you can bring back however many items from each category you want. Using this you could easily display the following:
Category 1
Cat1_Item1, Cat1_Item2 (37 more items…)
Category 2
Cat2_Item1, Cat2_Item2 (19 more items…)
Category 3
Cat3_Item1, Cat3_Item1 (140 more items…)
And that must be useful for somebody. All hail the great Simon!