Categories, items, limits and SQL…

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!

Out and about (15:24 pm)…

Exciting times ahead, phone-wise. I've asked my geek bits supplier to get me a 2gb memory card for my new phone. La inamorata is yet to be told…

Daddy speaks…

Tim Berners-Lee, the inventor of the World Wide Web, has been interviewed by the BBS. An excerpt of the interview is available, but I missed the full thing which was broadcast on TV last Tuesday.

In it he raises some interesting ideas about the nature of the web, despite having a bit of a roasting from the interviewer. But my favourite bit is this, regarding the state of the web in 20 years time:

Similarly the web will be, hopefully, will be something which is sunk into the background as an assumption. Now, if as technologists develop, we’ve done our job well, the web will be this universal medium, which will be very, very flexible. It won’t, itself, have any preconceived notions about what’s built on top.

One of the reasons that I want to keep it open like that, is partly because I want humanity to have it as a clean slate. My goal for the web in 30 years is to be the platform which has led to the building of something very new and special, which we can’t imagine now.

Hmmmm…

Not sure what happened there. Normal service will be resumed shortly…