Thread: Re: Return equal number of rows with same column value

On Jun 4, 5:27 pm, Nick <nboutel...@gmail.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

On Jun 4, 2012, at 21:06, Nick <nboutelier@gmail.com> wrote:

> On Jun 4, 5:27 pm, Nick <nboutel...@gmail.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.