[SQL] Oracle SQL - Get rows where it meets a percent allocation

I am working on selecting participants for a program based on certain criteria and after meeting the criteria, get x% (say 60%) from a specific department A, and the remaining 20% from another department B and the last 20% from department C. I am able to flag rows meeting the criteria in a column named flag_criteria and I do have the department names in the department column, however I don't know how I can get the rows based on the percentage allocation from the department column. As an example:



Now, based on the allocation %, I created a new column "Allocation", and of the 10 rows meeting the criteria, I assigned 60% from Dept A, 20% from B and 20% from C.



How would I handle this in SQL? Also, if there are no rows meeting the allocation %, I would want to give the "extra" allocations to the next dept. Please advise.

Continue reading...