So today a fellow staffer was trying to write the classic lilst of categories, with a sample of 5 items from each category query. This is a common problem, and I was sure there was some nice simple SQL subquery magic to make it all just work.

After a while however, he was still stuck, and given I couldn't remember the pricise incantations to make it all work, I asked him to paste me the query he had so far, and give me access to his database. I figured "a few minutes and I'll have this all sorted out"

Several hours, and much frustrated swearing at PostgreSQL later I had managed to involve several other staffers, none of whom could figure it out either. Something was definitely up, it just shouldn't be that difficult.

Anyway, the next thing I knew Geoff Cant (whom I'm sure uses the motto "why use one word when three will do") managed to create a solution. It used arrays in ways never seen before in PostgreSQL and looked something like ...

select e.* from item e inner join
(select category, array(select item from item where category=c.category limit 4) as t from category c) d
on (e.category = d.category and e.item = ANY(d.t))

After a bit of staring at this, I had a sudden epiphany. The subquery really belongs in there WHERE clause, and the whole thing becomes a lot simpler, behold ...

select category, item from category natural join item where item in (select item from item where category=category.category limit 4)

Now, this works exactly as it says on the can, except I'm not entirely sure the subquery isn't executed for every row (which wouldn't be terribly efficient), so if anyone has any further thoughts, feel free to join the madness with a comment below :-)

If you would like to see the sample data we used, you can grab it here