Order by statement with start value in a Round Robin Select

Question

I have a table in SQL Server where every row is a mail to deliver. Every email belongs to a domain.

I need to select the first email to send based on the Date column but I want to alternate them by domain starting from the last domain used in a Round Robin way

Table:

CREATE TABLE Delivery (Date datetime, Domain int);

INSERT INTO Delivery (Date, Domain)
VALUES
    ('2014-01-07 11:24:31', 1),
    ('2014-04-07 13:21:42', 2),
    ('2014-02-07 14:48:58', 3),
    ('2014-03-07 15:58:01', 1),
    ('2014-06-07 15:58:01', 2),
    ('2014-01-07 12:58:01', 3),
    ('2014-01-07 19:58:01', 1) ;

With this query I can sort them as I need but I cannot find a way to sort them with a starting value:

SELECT [Date],[Domain]
FROM (
  SELECT    [Date] ,[Domain],
  ROW_NUMBER() OVER (PARTITION BY [Domain] ORDER BY [Date]) AS recID
  FROM  Delivery ) AS r
ORDER BY    recID, [domain]

SqlFiddle

I need to say something like:

ORDER BY [domain] > @lastuseddomain

something similar to the mysql FIELD() function

I need to run this query at timed intervals with TOP 1 The expected result is to get the earliest row with domain > domain of previous row if exist or restart with domain=1.

Like a circular sorting on the domain

Maybe I"m the only one, but what are the results you're expecting? I don't understand the problem... So the expected ordering would be The earliest message of domain 1 then the earliest of domain 2 then the earliest of domain 3 then start over w/ domain 1 getting the next earliest (not previously processed?) — xQbert, Jun 06 '14 at 16:06
@xQbert yes, sorry for my bad english. I want that after getting the earliest message of domain 1 I want the earliest message starting from domain 2. — giammin, Jun 06 '14 at 16:10
Are the number of domains limited? Or will there be an arbitrary number of them? — simon at rcl, Jun 06 '14 at 16:25

score 1 · Answer 1 · answered Jun 06 '14 at 16:33

1

You need to use a cte
It allows you to use the ROW_NUMBER() as a where condition

with cte as 
(
  SELECT [Date] ,[Domain],
         ROW_NUMBER() OVER (PARTITION BY [Domain] ORDER BY [Date]) AS recID
  FROM  Delivery 
)
select * from cte 
 where recID = 1 
 order by domain

answered Jun 06 '14 at 16:33

paparazzo

44,497
23
105
176

if I set recID = 2 the first result is domain 1. It shold be the earliest of domain = 2 or next – giammin Jun 06 '14 at 16:38
I get that English is a second language but you are not clear. Did you try this? recID = 2 will be the second date from each domain – paparazzo Jun 06 '14 at 16:46
I need to select the earlier date with domain next to the previously selected row. This happens in 2 different time. – giammin Jun 06 '14 at 16:50
I run the query and get the first row. Then i run the query again and i need the earlier row with domain next to the previous row. – giammin Jun 06 '14 at 16:53
I don't follow but you have an answer – paparazzo Jun 06 '14 at 19:08

giammin · Accepted Answer · 2014-06-06T16:50:27.583

1

Thanks guys for your efforts but I think I find the solution:

SELECT TOP 1 [Date],[Domain]
FROM (
  SELECT [Date] ,[Domain], 
         ROW_NUMBER() OVER (PARTITION BY [Domain] ORDER BY [Date]) AS recID
  FROM  Delivery ) AS r
ORDER BY recID, 
   (CASE WHEN domain >@LASTUSEDDOMAIN THEN domain
         ELSE domain + (select top 1 domain from delivery order by domain desc) 
    END)

edited Jun 06 '14 at 16:50

answered Jun 06 '14 at 16:44

giammin

18,620
8
71
89

score 0 · Answer 3 · answered Jun 06 '14 at 16:36

EDIT -

I was totally wrong. Blam below has given you what really looks like the answer. Apologies!

Original post:

Given an arbitrary number of domains (and times) I don't see how this can be done in SQL I'm afraid. You would need row N in the result-set to set the conditions for selecting row N+1, and this can't be done. With a limited number of domains/times you might be able to do something with an UNPIVOT query, but I think you'd have to use dynamic SQL - SQL generated by the program with the knowledge of what the data to be processed is.

If that's correct, then you're probably wasting time generating a query and running (I can't see how it would be an efficient query) after you've got the data. Instead, get the data into some searchable list in code, and in code get the first, then with knowledge of what the first is get the second etc etc.

Sounds like a nasty bit of code, but the right searchable structure might be fun to work out.

Sorry I can't be more helpful, but I just don't see how it can be done.

Order by statement with start value in a Round Robin Select

3 Answers3