Binaryworld - A tricky ordering problem. ... [ SQL Server -> T-SQL ]

This is 2 of the solutions to an ORDER BY problem posed in the microsoft.sqlserver.programming newsgroups. This demonstrates several powerful derived table techniques.

Click here to copy the following block

create table #Numbers(DateEntered datetime, Number int)
go

insert #Numbers values('07/01/00 12:00:00 PM', 1)
insert #Numbers values('07/01/00 12:01:00 PM', 2)
insert #Numbers values('07/01/00 12:02:00 PM', 3)
insert #Numbers values('07/02/00 12:00:00 PM', 998)
insert #Numbers values('07/02/00 12:01:00 PM', 999)
insert #Numbers values('07/02/00 12:02:00 PM', 1)
insert #Numbers values('07/03/00 12:00:00 PM', 998)
insert #Numbers values('07/03/00 12:01:00 PM', 1)
insert #Numbers values('07/03/00 12:02:00 PM', 999)
insert #Numbers values('07/03/00 12:03:00 PM', 2)
insert #Numbers values('07/03/00 12:04:00 PM', 3)
go

/*
The nature of this data is as follows:

>> A Number will never be duplicated on the same *day*.
>> The Number values are sequenced 1-999, wrapping back to 1 after 999
>> (1, 2, 3, 4, ..., 997, 998, 999, 1, 2, 3, ...)
>> There may be gaps in the data.
>> The data in the Numbers table can be *entered* out of order as in the
>> 07/03/00 examples
>> If 999 occurs in the same *day* as 1, everything prior to 999 must be
>> ordered *before* 1 and greater.
>> The numbers will always be within a 500-number delta
*/

-- To understand the query, you can analyze the output of
-- each derived table separately.
SELECT nn.DateEntered , nn.Number
FROM (
SELECT n.DateEntered , n.Number ,
    -- If 1 & 999 occur on same day And
    -- If ( Number < 999 & DateEntered is prior to Number = 1 ) Or
    -- ( Number = 999 ) then use DateEntered of Number = 1 itself
    CASE WHEN ( n.SameDay = 1 ) And
( ( n.Number < 999 And n.DateEntered < n.OneDateEntered ) Or
   ( n.Number = 999 ) )
THEN n.OneDateEntered
END AS NewDateEntered ,
-- If 1 & 999 occur on same day, offset 1 to 1000 for sorting
CASE WHEN SameDay = 1 And n.Number = 1
        THEN 1000
    END AS NewNumber
FROM (
SELECT n1.DateEntered , n1.Number ,
    -- Mark rows where 1 & 999 occur on the same day
    ( CASE WHEN ( SELECT COUNT( * ) FROM #Numbers n2
         WHERE n2.Number in ( 999 , 1 ) And
   CONVERT( varchar , n1.DateEntered , 101 ) =
    CONVERT( varchar , n2.DateEntered , 101 ) ) = 2
THEN 1
     END ) AS SameDay ,
-- Get DateEntered for Number = 1, used later.
    ( SELECT n4.DateEntered FROM #Numbers n4
  WHERE CONVERT( varchar , n1.DateEntered , 101 ) =
CONVERT( varchar , n4.DateEntered , 101 ) And
n4.Number = 1
    ) AS OneDateEntered
FROM #Numbers as n1
) AS n
) AS nn
-- Use the NewDateEntered and NewNumber values if present for sorting
ORDER BY COALESCE( nn.NewDateEntered , nn.DateEntered ) ,
     COALESCE( nn.NewNumber , nn.Number )

-- The query below was suggested by SQL Server MVP "BP Margolin"
SELECT DateEntered, Number
FROM (SELECT n1.*, minNumber, maxNumber
   FROM #Numbers as n1
      -- Find min & maximum number for each day
   JOIN (SELECT DateEntered = convert(char(10), DateEntered, 102),
            minNumber = MIN( Number ),
            maxNumber = MAX( Number )
      FROM #Numbers
      GROUP BY convert(char(10), DateEntered, 102)
   ) AS n2
   ON convert(char(10), n1.DateEntered, 102) =
      convert(char(10), n2.DateEntered, 102)
) AS n3
ORDER BY convert(char(10), DateEntered, 102),
     -- Since the values will be within 500-number DELTA , use that to
     -- offset values from 1 to 499
     CASE WHEN maxNumber <= minNumber + 500 THEN Number ELSE NULL END ,
     -- offset values from >= 500
     CASE WHEN maxNumber > minNumber + 500
         THEN ( CASE WHEN Number >= maxNumber - 500
               THEN Number
               ELSE Number + 1000
            END )
         ELSE NULL
     END