It's hard to explain by words, so i'll try with an example:
| Name | Age | Sex |
|---|---|---|
| Liam | 20 | M |
| William | 21 | M |
| Emma | 21 | F |
| Oliver | 22 | M |
| Sophia | 22 | F |
| Isabella | 23 | F |
| Mia | 23 | F |
| Olivia | 24 | F |
| James | 24 | M |
I have to try to balance the distribution of males and females.
I need to sort this table by Age and then by Sex, in a way that the least present sex so far comes first. In my example Emma should come before William (they both are 21 but so far there have been one M), Sophia should come before Oliver (they both are 22 but so far there have been two M e one F) and James should come before Olivia (they both are 24 but so far there have been three M and four F)
I tried with OVER and PARTITION BY but I haven't been able to get a valid result
Will this work for you? Any time you need to do a running/windowing aggregation you'll need the ROWS BETWEEN clause.
SELECT name,
age,
sex,
COUNT(*) OVER (PARTITION BY sex ORDER BY age ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sex_count
FROM table
ORDER BY age,sex_count