Group by and limit

Reposting as I noticed that the original was in reply to a different subject.


Hey Folks – have a coded myself into a corner yet?

I have a situation with a select count / group by / order by query that I need to limit each group to 500
entries. Not seeing a way to do this in a single query, do I need to use multiple queries?
Group x has about 200 entries in it; group y has about 5-8k per x.

select x, y, count(*) as counter from mytable
group by x, y
order by x, counter, y


I only want the first 500 for each x.

Any tips or tricks someone might know would be appreciated.

I’m using postgres 8.3.7.