Using different order by with union

sql, sql-server, t-sql

Solution

This should work:

SELECT * 
FROM (SELECT TOP 10 A.*, 0 AS Ordinal
      FROM A
      ORDER BY [Price]) AS A1

UNION ALL

SELECT * 
FROM (SELECT TOP 3 A.*, 1 AS Ordinal
      FROM A
      ORDER BY [Name]) AS A2

ORDER BY Ordinal

From MSDN:

In a query that uses UNION, EXCEPT, or INTERSECT operators, ORDER BY is allowed only at the end of the statement. This restriction applies only to when you specify UNION, EXCEPT and INTERSECT in a top-level query and not in a subquery.

Edited: to force the order you need to apply an `ORDER BY` to the outer query. I've added a constant value column to both queries.

Problem

I want to write a query like ``` select top 10 * from A order by price union select top 3 * from A order by price ``` or sth like that ``` select top 10 * from A where name like '%smt%' order by price union select top 3 * from A where name not like '%smt%' order by price ``` Can you please help me?

Original source