order by in union query

[Kuttan]

Hi

Is it possible to apply order by for a union query and how.

eg:

create table one(id int auto_increment, v_date date, name varchar(20),
amount double)

insert into one values(default, '2010-01-04', 'abcd', 400)
insert into one values(default, '2010-01-04', 'efgh', 400)
insert into one values(default, '2010-01-04', 'ijkl', 400)
insert into one values(default, '2010-01-05', 'abcd', 500)
insert into one values(default, '2010-01-05', 'efgh', 500)
insert into one values(default, '2010-01-05', 'ijkl', 500)
insert into one values(default, '2010-01-06', 'abcd', 600)
insert into one values(default, '2010-01-06', 'efgh', 600)
insert into one values(default, '2010-01-06', 'ijkl', 600)

now i run the below queries:
Query 1
======
select id, name sum(amount) from one where v_date between '2010-01-05'
and '2010-01-06' group by id, name order by name
Query 2
======
select id, name sum(amount) from one where v_date < '2010-01-05' group
by id, name order by name

required result
============
the result obtained in query 1 and query 2 should be obtained by
running 1 query some thing like
select id, name sum(amount) from one where v_date between '2010-01-05'
and '2010-01-06' group by id, name order by name union select id, name
sum(amount) from one where v_date < '2010-01-05' group by id, name
order by name

Thanking you

Regds - K. Prakash

[Dario Fassi]

Hi,
try with:

select * from (
 select id, name, sum(amount) as mysum from one 
 where v_date between '2010-01-05' and '2010-01-06' 
 group by id, name 
union 
 select id, name, sum(amount) as mysum from one 
 where v_date < '2010-01-05' 
 group by id, name
) x 
order by name
;

regards
Dario

El 02/12/10 10:13, Kuttan escribió:

[Thomas Mueller]

Hi,

This works as well:

(select id, name, sum(amount) from one

where v_date between '2010-01-05' and '2010-01-06'
group by id, name

union
select id, name, sum(amount) from one

where v_date < '2010-01-05'
group by id, name)

order by name;

The complete test case:

drop table one;

create table one(id int primary key,
v_date date, name varchar(20), amount int);

insert into one values(1, '2010-01-04', 'abcd', 400);
insert into one values(2, '2010-01-04', 'efgh', 400);
insert into one values(3, '2010-01-04', 'ijkl', 400);
insert into one values(4, '2010-01-05', 'abcd', 500);
insert into one values(5, '2010-01-05', 'efgh', 500);
insert into one values(6, '2010-01-05', 'ijkl', 500);
insert into one values(7, '2010-01-06', 'abcd', 600);
insert into one values(8, '2010-01-06', 'efgh', 600);
insert into one values(9, '2010-01-06', 'ijkl', 600);

(select id, name, sum(amount) from one

where v_date between '2010-01-05' and '2010-01-06'
group by id, name

union
select id, name, sum(amount) from one

where v_date < '2010-01-05'
group by id, name)

order by name;

Regards,
Thomas