Order using terms in the where clause

Question

I have a simple select request -

SELECT ID, NAME 
FROM PERSONS
WHERE NAME IN ('BBB', 'AAA', 'ZZZ')
-- ORDER BY ???

I want this result to be ordered by the sequence in which NAMIs are indicated, i.e. The first row in the result set should be with NAME = BBB, the second - AAA, 3rd it ZZZ.

Is this possible on a SQL server? I would like to know how to do this if there is a simple and short way to do this, for example, 5-6 lines of code.

Answer 1

You can create an ordered split function:

CREATE FUNCTION [dbo].[SplitStrings_Ordered]
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT [Index] = ROW_NUMBER() OVER (ORDER BY Number), Item 
    FROM (SELECT Number, Item = SUBSTRING(@List, Number, 
      CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
     FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
      FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2) AS n(Number)
      WHERE Number <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
    ) AS y);

Then change your input a bit (one comma-separated list instead of three separate lines):

SELECT p.ID, p.NAME 
FROM dbo.PERSONS AS p
INNER JOIN dbo.SplitStrings_Ordered('BBB,AAA,ZZZ', ',') AS s
ON p.NAME = s.Item
ORDER BY s.[Index];

Answer 2

You can save names in temp table with order. Example:

DECLARE @Names TABLE (
    Name VARCHAR(MAX),
    SortOrder INT
)

INSERT INTO @Names (Name, SortOrder) VALUES ('BBB', 1)
INSERT INTO @Names (Name, SortOrder) VALUES ('AAA', 2)
INSERT INTO @Names (Name, SortOrder) VALUES ('ZZZ', 3)

SELECT P.ID, P.NAME 
FROM PERSONS P
JOIN @Names N ON P.Name = N.Name
ORDER BY N.SortOrder

Answer 3

There is no way to do this using the order in the predicate IN, however you can create a table of constants giving your constants an order by value:

SELECT  p.ID, p.NAME 
FROM    PERSONS p
        INNER JOIN
        (   VALUES
                ('BBB', 1),
                ('AAA', 2),
                ('ZZZ', 3)
        ) t (Name, SortOrder)
            ON p.Name = t.Name
ORDER BY t.SortOrder;

Another (and in my case less attractive) solution is to use CASE

SELECT  ID, NAME 
FROM    PERSONS
WHERE   NAME IN ('BBB', 'AAA', 'ZZZ')
ORDER BY CASE Name
            WHEN 'BBB' THEN 1
            WHEN 'AAA' THEN 2
            WHEN 'ZZZ' THEN 3
        END;

Answer 4

SELECT ID, NAME 
FROM PERSONS
WHERE NAME IN ('BBB', 'AAA', 'ZZZ')
ORDER BY CASE 
              WHEN NAME = 'BBB' THEN 1 
              WHEN NAME = 'AAA' THEN 2
              WHEN NAME = 'ZZZ' THEN 3
           END ASC

Answer 5

I think this should work:

ORDER BY CASE 
   WHEN NAME = 'BBB' THEN 0 
   WHEN NAME = 'AAA' THEN 1
   WHEN NAME = 'ZZZ' THEN 2
   ELSE 3 
   END ASC