Ordering data based on a rotation

Question

Suppose I have a dataset: ID NEXT ORDER 1456 1679 1 1679 1679 2 1578 1679 3 9492 1679 4

The only column in this dataset that ever changes is NEXT. ID is always unique, NEXT always correlates to a single ID and will be duplicated throughout every row. ORDER is a sequential int.

I need an orderby, or other SQL statement that will perform the following sort:

---

The NEXT ID is always at the bottom of the ordering.

The ID that has the sequentially greater ORDER after NEXT's ORDER is always at the top

The ordering is then sequential starting with the second to top row's ORDER being the next after The top row's order

---

Example:

for the above table the ordering would be 1578 9492 1456 1679

---

Another table where next has changed:

ID NEXT ORDER 1456 1578 1 1679 1578 2 1578 1578 3 9492 1578 4

would be:

9492 1456 1679 1578

Answer 1

Similar to Joel's answer only using window function instead of INNER JOIN or CROSS APPLY:

SELECT
    *
FROM
    #temp t
ORDER BY
    CASE
       WHEN t.[Order] > MAX(CASE WHEN ID = [NEXT] THEN [ORDER] END) OVER () THEN -999999999 + t.[Order]
       ELSE t.[Order]
    END

And here is a version using CROSS APPLY

SELECT
    t.*
FROM
    #temp t
    CROSS APPLY (SELECT [Order] FROM #temp WHERE ID = NEXT) n
ORDER BY
    CASE
       WHEN t.[Order] > n.[Order] THEN -99999999 + t.[Order]
       ELSE t.[Order]
    END

Answer 2

-- Exsting query
select ...
from ...

-- New
INNER JOIN (select top 1 [ORDER] FROM [MyTable] WHERE ID=[Next]) o ON 1=1

-- Rest of existing query
WHERE ...

--ORDER BY clause
ORDER BY CASE WHEN [order] > o.[Order] THEN -9999999+[order] else [order] end

SQLFiddle
(Fiddle uses Postgresql, as Sql Server seems to be broken at the moment there).

Answer 3

This should work:

SAMPLE DATA:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp;

CREATE TABLE #temp(ID      INT
              , [NEXT]  INT
              , [ORDER] INT);

INSERT INTO #temp
VALUES
      (1456
     , 1679
     , 1),
      (1679
     , 1679
     , 2),
      (1578
     , 1679
     , 3),
      (9492
     , 1679
     , 4);

QUERY:

DECLARE @rowcount INT;

SELECT @rowcount = COUNT(1)
FROM   #temp
WHERE  [ORDER] >
              (SELECT [ORDER]
               FROM   #temp
               WHERE  ID = [NEXT]);

SELECT ID
FROM
         (SELECT *
              , rn = ROW_NUMBER() OVER(ORDER BY [ORDER])
          FROM   #temp
          WHERE  [ORDER] >
                        (SELECT [ORDER]
                        FROM   #temp
                        WHERE  ID = [NEXT])
                        UNION
                        SELECT *
                            , rn = @rowcount + ROW_NUMBER() OVER(ORDER BY [ORDER])
                        FROM   #temp
                        WHERE  [ORDER] <=
                                      (SELECT [ORDER]
                                       FROM   #temp
                                       WHERE  ID = [NEXT])) AS A
ORDER BY rn;

RESULT: