sql server - Priority queue in SQL -

i implementing queueing system multiple priorities.
query can return x rows at least y rows of each priority.

for example:

lets queue has 3 priorities (high, medium , low), , 3, 2 , 1 rows each priority respectively.

if table looked this:

----------------- | id | priority | ----------------- |  1 |     high | |  2 |     high | |  3 |     high | |  4 |     high | |  5 |   medium | |  6 |   medium | |  7 |      low | ----------------- 

three simple queries unioned return (1, 2, 3, 5, 6, 7).

select top 3 id tbl priority = 'high' union select top 2 id tbl priority = 'medium' union select top 1 id tbl priority = 'low' 

however problem occurs when table doesn't contain enough of particular priority:

----------------- | id | priority | ----------------- |  1 |     high | |  2 |     high | |  3 |     high | |  4 |     high | |  5 |   medium | |  6 |      low | |  7 |      low | ----------------- 

i have return (1, 2, 3, 4, 5, 6).
using highest priority fill in gaps (in case, using 4th high row there not enough mediums).

is there query accommodate this, or better filtering inside application rather @ sql level?

you can write stored procedure in loop (3 times, 1 each priority, starting lowest) and, @ each iteration, dynamically adjust number of values returned in next iteration (higher priority) if there not enough in one.

with dynamically mean:

select top (@count) * sometable 

where count adjusted if necessary in previous iteration.

the problem having in application logic either have fetch more data (3 times maximum counter used in top) such can have enough data fill slots, or have communicate db again.

for small numbers, preventive fetching not important. preference have in stored procedure.

but there number of variables play role here: size of individual rows fetch, size of table search in , indexes, program architecture, network configuration, counter values, number of priorities, etc., etc.