Using different order by with union

问题:

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?

回答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.

回答2:

This is a real hacky way to do this. You probably want these as separate queries in reality, but this should give you the result you want...

select *
from (
  select top 10 *, 1 as 'ord', price as 'ordprice' from A
  union 
  select top 3 *, 2 as 'ord', 0 as 'ordprice' from A
) a
order by ord, ordprice, name

回答3:

UNION doesn't like ORDER by clauses in the UNIONed expressions. Try this:

SELECT * FROM
   (SELECT TOP 10 * FROM A ORDER BY Price) SetA
UNION
SELECT * FROM
   (SELECT TOP 3 * FROM a ORDER BY name) Setb
[ORDER BY something]

This spoofs the UNION operator into ignoring the ORDER BYs, which still operate correctly on the TOP operator. You can apply a final ORDER BY to order the UNIONed set, if you like.

[No longer applies exactly to your question now that it's edited!]

回答4:

select top 10 *,0 as RS from A
union
select top 3 *,1 as RS from A 
order by
    RS,
    CASE WHEN RS=0 THEN price END, --Don't affect RS 1
    name

回答5:

cmd.CommandText = "SELECT 0 AS Employee_ID, 'No Employees' as Employee_FullName , 'id1' Orderkey UNION ALL SELECT Employee_ID, Employee_FullName, 'id2' Orderkey FROM tblEmployee ORDER BY Orderkey, Employee_FullName"

ds = dbconn.SelectQuery(cmd)

ds.Tables(0).Columns.Remove(ds.Tables(0).Columns("Orderkey"))