Skip to content
Advertisement

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:

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:

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:

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?

Advertisement

Answer

You don’t need the UNION you can use

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement