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