SQL Ordering records by "weight"

Question

We have a system that processes records by a "priority" number in a table. We define the priority by the contents of the table, e.g.

UPDATE table
SET priority=3
WHERE processed IS NULL

UPDATE table
SET priority=2
WHERE balance>50

UPDATE table
SET priority=1
WHERE value='blah'

(please ignore the fact that there could be 'overlaps' between priorities :) )

This works fine - the table is processed in priority order, so all the rows where the column "value" is 'blah' are worked first.

I've been given the task of adding an option to order the records by a definable "weight". For example, we'd like 50% of the processing to be priority 1, 25% priority 2 and 25% priority 3. Therefore, from the above, in every 100 records 50 of them would be ones where "value" is 'blah", 25 of them would be where "balance" is greater than 50 etc.

I'm trying to figure out how to do this: some kind of weighted incrementing value for "priority" would seem to be the best way, but I can't get my head around how to code this. Can anyone help please?

EDIT: Apologies, should have said: this is running on MSSQL 2008

Answer 1

General idea is to collect tasks into buckets, divided on border of whole numbers:

select
  task_id
from (  
  select 
    task_id, 
    ((task_priority_order - 1) / task_priority_density) as task_processing_order
  from (
    select
      t.task_id                                            as task_id, 
      t.priority                                           as task_priority, 
      row_number() 
        over (partition by t.priority order by t.priority) as task_priority_order,
      case
        when t.priority = 3 then 50
        when t.priority = 2 then 25
        when t.priority = 1 then 25
      end                                                  as task_priority_density
    from
      table t
  )
)
order by task_processing_order

In the diapason from 0.0 to 0.(9) we got 100 records constructed from first 50 records with priority 3, first 25 records with priority 2 and first 25 records with priority 1.

The next diapason from 1.0 to 1.(9) represents next bucket of records.

If no more tasks with some value of priority then remaining tasks will be placed in buckets in same ratio. E.g. if not enough tasks with priority 3 then remaining tasks will be arranged with ratio of 50/50.

task_id - some surrogate key for task identification.

P.S. Sorry, I can't test this query now, so any syntax correction very appreciated.

Update: Query syntax corrected according to comments.