Обсуждение: 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.

Reid Thompson <reid.thompson@ateb.com> writes:
> 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.

  http://troels.arvin.dk/db/rdbms/#select-top-n

Consider using a more recent version of PostgreSQL, equipped with window
functions!

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support