Order by statement with start value in a Round Robin Select

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

Answers


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

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)

Need Your Help

Unresolved overloading error in Haskell

haskell overloading

getNext a = if even a then a/2 else (3*a)+1

How to bulk update in sql server 2008

asp.net sql-server-2008 bulk batch-updates

i have a table which contains 10000 records, now i have added the field for placing the plaintext password, so now i want to update all the records, but should update the new field on the basis of ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.