0

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

Lelehaine
  • 13
  • 2
  • Seems like a task that is way easier to solve on the application side. Are you sure you **need** this to be done in SQL? – markalex Apr 21 '23 at 19:06
  • I would prefer to solve it with a query, if possible. This is also a way to see how complex a query can be. If it's impossilbe to solve with a query, i'll find a way to solve it on the application side. – Lelehaine Apr 21 '23 at 19:09

1 Answers1

2

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
Paul W
  • 5,507
  • 2
  • 2
  • 13
  • YEAH, thank you so much, i did also try the "COUNT(*) OVER (PARTITION BY sex)" but it didn't work. The "ORDER BY" and "ROWS BETWEEN" does the trick! – Lelehaine Apr 22 '23 at 07:26