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?

In what order do you want the final result? If there are rows that appear in both subselects, should they appear twice in the final result or just once? — Mark Byers
– Mark Byers, Commented Nov 19, 2012 at 13:30

Tim Schmelter · Accepted Answer · 2012-11-19 13:55:51Z

29

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.

edited Nov 19, 2012 at 13:55

answered Nov 19, 2012 at 13:32

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Derek Over a year ago

I'm under the impression that the OP would want them ordered in the final result (the 10 price followed by the 3 name) and I don't think this can 100% guarantee that result

Damien_The_Unbeliever Over a year ago

There is no guarantee on the order in which results are returned by a UNION ALL (nor UNION) unless it has an ORDER BY clause applied.

Tim Schmelter Over a year ago

@Damien_The_Unbeliever: Does that mean that it's not guaranteed that both queries are appended?

Damien_The_Unbeliever Over a year ago

No, it means that it could return the 1st row from the first query, then the 1st and 2nd rows from the second query, then the 2nd, 3rd and 4th rows from the first query, 3rd row from second query, and then the remaining rows from the first query. And, in fact, it's not even guaranteed to return them in the sort orders of the subqueries - it could, for whatever reason, choose to reorder the results. If you don't have an ORDER BY on the outer query, you can assume nothing about the final results

Tim Schmelter Over a year ago

@Damien_The_Unbeliever: Would it help to add a foo column which we can order by? For example a constant numerical value in the first query and a higher in the second query. Edited my answer accordingly.

|

Derek · Accepted Answer · 2012-11-19 13:32:55Z

4

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

answered Nov 19, 2012 at 13:32

Derek

23.5k8 gold badges55 silver badges61 bronze badges

Comments

sebt · Accepted Answer · 2012-11-19 13:36:57Z

2

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!]

answered Nov 19, 2012 at 13:36

sebt

5253 silver badges8 bronze badges

Comments

Damien_The_Unbeliever · Accepted Answer · 2012-11-19 13:33:37Z

0

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

answered Nov 19, 2012 at 13:33

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Comments

ben · Accepted Answer · 2018-06-19 12:14:49Z

-2

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

answered Jun 19, 2018 at 12:14

ben

1

1 Comment

Joao Vitorino Over a year ago

Welcom to SO. Please read how to answer

Collectives™ on Stack Overflow

Using different order by with union

5 Answers 5

7 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

7 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related