CODEWITHSUNDEEP
sqlpostgresql
Sam Oliver
Sam Oliver

Reputation: 1194

How to select an equal number of rows for two field values?

Given the following table:

| animal | id |
---------------
| Dog    | 1  |
| Cat    | 2  |
| Dog    | 3  |
| Dog    | 4  |
| Cat    | 5  |
| Dog    | 6  |
| Dog    | 7  |
| Dog    | 8  |
| Cat    | 9  |
| Dog    | 10 |

Without using a UNION, is there any way to select rows to return an equal number of cats and dogs, for example select 3 of each:

| animal | id |
---------------
| Dog    | 1  |
| Cat    | 2  |
| Dog    | 3  |
| Dog    | 4  |
| Cat    | 5  |
| Cat    | 9  |

If an even number of each animal cannot be produced from the available rows, it should be as close as possible to even.

Upvotes: 0

Views: 126

Answers (1)

Michel Milezzi
Michel Milezzi

Reputation: 11155

You could use row_number with partition by animal:

CREATE TABLE sample(animal, id) AS 
    VALUES 
        ('Dog', 1),
        ('Cat', 2),
        ('Dog', 3),
        ('Dog', 4),
        ('Cat', 5),
        ('Dog', 6),
        ('Dog', 7),
        ('Dog', 8),
        ('Cat', 9),
        ('Dog', 10);

WITH tmp AS (
    SELECT 
        *, 
        row_number() OVER (PARTITION BY animal ORDER BY id) AS ordinal 
    FROM 
        sample 
)
SELECT * FROM tmp WHERE ordinal <= 3;

Upvotes: 2

Related Questions