T-SQL Union (distinct, not all) and Order by with priority

Assume I have a Tag table (Id int and Name nvarchar(100)) with 3 values:

1 Software
2 Hardware
3 Warehouse

Now I want to query with keyword, and prioritize the ones that starts with the keyword over the containing one. So at first I write this query:

SELECT 0 AS SortCol, * 
FROM Tag T
WHERE CHARINDEX(@keyword, T.Name) = 1
UNION
SELECT 1 AS SortCol, * 
FROM Tag T
WHERE T.Name LIKE ('%' + @keyword + '%')
ORDER BY SortCol, Name;

However that did not work because the SortCol column no longer make them distinct (the Warehouse value appears twice because it is correct in both).

That’s when I think I need to manually call DISTINCT by getting rid of that column:

SELECT DISTINCT T2.Id, T2.Name
FROM
    (SELECT 0 AS SortCol, * 
     FROM Tag T
     WHERE CHARINDEX(@keyword, T.Name) = 1
     UNION
     SELECT 1 AS SortCol, * 
     FROM Tag T
     WHERE T.Name LIKE ('%' + @keyword + '%')
     ORDER BY SortCol, T.Name) AS T2;

However, this does not work because I get this error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

What am I missing here? How can I use UNION with ORDER BY in both statements?

Answer

You don’t need the UNION you can use

SELECT *
FROM   Tag T
WHERE  T.Name LIKE '%' + @keyword + '%'
ORDER  BY CASE WHEN T.Name LIKE @keyword + '%' THEN 0 ELSE 1 END,
          Name;

Advertisement

Answer