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.