Hi,
I am trying to find out whether it is possible to write a SELECT query that provides fair queuing.
Imagine a table with 5 fields - MessageID, UserID, Message, Destination, Status.
What I want to do is select all messages that have a status of "Pending", but I want it to be ordered to promote fairness. An example select (with no ordering, so ordered by creation time) might start with 1,000 messages from user ID 1, followed by 2 messages from user ID 2, followed by 400 from user ID 3 etc.
The problem is that it might take two or three seconds to process each message, so user ID 2 may have entered 2 messages seconds after user ID 1 had entered 1000, but user ID 2 will have to wait for user ID 1's messages to be processed.
Ideally, I would like an SQL statement that would return 1 from user ID 1 then 1 from user ID 2, then one from user ID 3, then another from user ID 1, then user ID 2, then user ID 3, then skipping between user IDs 1 and 3.
The closest that I can think of is to group by user ID, but this means I get a maximum of one result per user ID which is no good to me
Cheers,
Ben
I am trying to find out whether it is possible to write a SELECT query that provides fair queuing.
Imagine a table with 5 fields - MessageID, UserID, Message, Destination, Status.
What I want to do is select all messages that have a status of "Pending", but I want it to be ordered to promote fairness. An example select (with no ordering, so ordered by creation time) might start with 1,000 messages from user ID 1, followed by 2 messages from user ID 2, followed by 400 from user ID 3 etc.
The problem is that it might take two or three seconds to process each message, so user ID 2 may have entered 2 messages seconds after user ID 1 had entered 1000, but user ID 2 will have to wait for user ID 1's messages to be processed.
Ideally, I would like an SQL statement that would return 1 from user ID 1 then 1 from user ID 2, then one from user ID 3, then another from user ID 1, then user ID 2, then user ID 3, then skipping between user IDs 1 and 3.
The closest that I can think of is to group by user ID, but this means I get a maximum of one result per user ID which is no good to me
Cheers,
Ben