CODEWITHSUNDEEP
sqlsql-servert-sqlsql-order-byunion
Luke Vo
Luke Vo

Reputation: 20658

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

enter image description here

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?

Upvotes: 1

Views: 116

Answers (3)

GMB
GMB

Reputation: 222442

Just put a CASE in the ORDER BY clause directly, like :

SELECT
    *
FROM Tag
WHERE name like ( '%' + @keyword + '%' ) 
ORDER BY
    CASE 
        WHEN CHARINDEX(@keyword, T.Name) = 1 THEN 0
        ELSE 1
    END,
    names

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

If for some reason you need this SortCol column it is possible:

DECLARE @keyword NVARCHAR(MAX) = N'ware';

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name) rn
  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 + '%')) s
)
SELECT SortCol, Id, name
FROM cte
WHERE rn = 1
ORDER BY SortCol, Name;

db<>fiddle demo

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453142

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;

Upvotes: 5

Related Questions