CODEWITHSUNDEEP
mysqlsqlunion
Devisy
Devisy

Reputation: 159

How to sort results from UNION

I have a query like this:

(select @number:=3) 
  union 
(select @number:=2) 
  union 
(select @number:=1) 
order by @number ASC

With results:

3
2
1

But I would like the results in ascending order, like this:

1
2
3

How can I achieve the results in ascending order with a query like this?

Upvotes: 0

Views: 796

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is your query:

(select @number:=3) 
  union 
(select @number:=2) 
  union 
(select @number:=1) 
order by @number ASC

Your order by has a constant. It is order by "1" -- @number is a variable, not a column name. Hence, no ordering. What you want is to specify the number as a column name:

select 3 as number
union all
select 2
union all
select 1
order by number;

You should also use union all instead of union, unless you want the additional overhead of removing duplicates.

Upvotes: 0

AdamMc331
AdamMc331

Reputation: 16691

You can wrap the UNION in a subquery, try this:

SELECT *
FROM(
   SELECT @number := 3 AS number
   UNION
   SELECT @number := 2 AS number
   UNION
   SELECT @number := 1 AS number) tmp
ORDER BY number;

Here is an SQL Fiddle example.

An edit, to explain what is happening:

In your example, MySQL is treating each group as its own query (which is how you'd expect a union to work) so it is as if you had three different queries, and only the third one is being ordered.

So, by putting the unioned queries together, you have one result set, and that entire result set is what is being ordered.

Upvotes: 2

Related Questions