SQL query for Top 5 of every category

Question

Hi there,

I have a table that has three columns: Category, Timestamp and Value.

What I want is a SQL select that will give me the 5 most recent values of each category. How would I go about and do that?

I tried this:

select
  a."Category",
  b."Timestamp",
  b."Value"
from
(select "Category" from "Table" group by "Category" order by "Category") a,
(select a."Category", c."Timestamp", c."Value" from "Table" c
where c."Category" = a."Category" limit 5) b

Unfortunately, it won't allow it because "subquery in FROM cannot refer to other relations of same query level".

I'm using PostGreSQL 8.3, by the way.

Any help will be appreciated.

Answer 1

SELECT t1.category, t1.timestamp, t1.value, COUNT(*) as latest
FROM foo t1
JOIN foo t2 ON t1.id = t2.id AND t1.timestamp <= t2.timestamp
GROUP BY t1.category, t1.timestamp
HAVING latest <= 5;

Note: Try this out and see if it performs suitably for your needs. It will not scale well for large groups.