Skip site navigation (1) Skip section navigation (2)

Re: Return equal number of rows with same column value

From: David Johnston <polobo(at)yahoo(dot)com>
To: Nick <nboutelier(at)gmail(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Return equal number of rows with same column value
Date: 2012-06-05 05:05:56
Message-ID: FB0B833C-78E8-4769-96A6-004CB42257EF@yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
On Jun 4, 2012, at 21:06, Nick <nboutelier(at)gmail(dot)com> wrote:

> On Jun 4, 5:27 pm, Nick <nboutel(dot)(dot)(dot)(at)gmail(dot)com> wrote:
>> For the data...
>> 
>> INSERT INTO test (id,val) VALUES (1,a);
>> INSERT INTO test (id,val) VALUES (2,a);
>> INSERT INTO test (id,val) VALUES (3,a);
>> INSERT INTO test (id,val) VALUES (4,a);
>> INSERT INTO test (id,val) VALUES (5,b);
>> INSERT INTO test (id,val) VALUES (6,b);
>> 
>> How could I return an even amount of val? For example, I would like to
>> return this...
>> 
>> 1 | a
>> 2 | a
>> 5 | b
>> 6 | b
>> 
>> Since the least number of b vals is 2, Id like to limit the a columns
>> to return only 2
> 
> Found something good. Now if I could only dynamically get that "2"
> least val.
> 
> SELECT * FROM (
>  SELECT ROW_NUMBER() OVER (PARTITION BY val ORDER BY val) AS r, t.*
> FROM test t
> ) x
> WHERE r <= 2
> 

... where r <= (select min(select count(*) from test group by val))

David J.


In response to

pgsql-general by date

Next:From: Thomas MarkusDate: 2012-06-05 08:13:49
Subject: Re: Procedural Languages
Previous:From: Trevor CampbellDate: 2012-06-05 03:49:24
Subject: Re: Trouble with plan statistics for behaviour for query.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group