order by using terms in where clause

I have a simple select query –

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 NAMES are provided, that is,
1st row in result set should be the one with NAME = BBB, 2nd is AAA, 3rd it ZZZ.

  • Why connecting to an OLEDB is giving me an connection error
  • SQL Server 2008 R2: Prepare dynamic WILDCARD
  • Sql server table usage statistics
  • Jet-SQL to TSQL
  • How can i replace content under a specific URL using SQL?
  • T-SQL Is a sub query for an Update restriction Atomic with the update?
  • Is this possible in SQL server ? I would like to know how to do it if there is a simple and short way of doing it, like maybe 5-6 lines of code.

    5 Solutions collect form web for “order by using terms in where clause”

    You could 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 alter your input slightly (a single comma-separated list instead of three individual strings):

    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];
    

    You could store the names in a temp table with an 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
    

    There is no way to do this using the order in the IN predicate, however, you could 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;
    

    The other (and in my option 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;
    
    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
    

    I think this must work:

    ORDER BY CASE 
       WHEN NAME = 'BBB' THEN 0 
       WHEN NAME = 'AAA' THEN 1
       WHEN NAME = 'ZZZ' THEN 2
       ELSE 3 
       END ASC
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.