Using different order by with union

Question

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?

Answer 1

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.