Phillip Byram Phillip Byram - 3 months ago 18
SQL Question

Ordering data based on a rotation

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
-- 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).

Comments