SQL Server : how to order by using a starting value

Question

Query to order a table by a value starting at a certain value.

Current:

ZQE123
ABC123
ABC324
ALP234

If I order by from variable 'ABC324'

ABC324
ALP234
ZQE123
ABC123

If I order by from variable 'ZZZ123'

ABC123
ABC324
ALP234
ZQE123

It's basically a ordered circular list depending on which variable is specified.

I've tried using the RANK() function, but haven't had too much luck.

SELECT 
    Location, 
    RANK() OVER (ORDER BY Location) 
FROM Table1

Output:

ZQE123    4 
ABC123    1
ABC324    2 
ALP234    3

How can I get it to do it based on the current location?

Answer 1

ORDER  BY CASE
            WHEN YourCol >= @Variable THEN 0
            ELSE 1
          END,
          YourCol 

Would be one way. Or another (assumes no nulls)

WITH CTE
     AS (SELECT 1 AS Part,
                *
         FROM   T
         WHERE  YourCol >= @Variable
         UNION ALL
         SELECT 2 AS Part,
                *
         FROM   T
         WHERE  YourCol < @Variable)
SELECT *
FROM   CTE
ORDER  BY Part,
          YourCol 

Answer 2

Just another option, though I like Martin Smith's answer:

DECLARE @Start CHAR(6) = 'ABC324';

DECLARE @T TABLE (Val CHAR(6));
INSERT INTO @T VALUES ('ZQE123'), ('ABC123'), ('ABC324'), ('ALP234');

DECLARE @Count INT;
SELECT @Count = COUNT(1) FROM @T;


;WITH Ranked AS
(
    SELECT *, RANK() OVER (ORDER BY Val) Ord FROM @T
)
SELECT Val FROM Ranked
ORDER BY CASE WHEN Val >= @Start THEN Ord ELSE Ord + @Count END;