Subquery Order by in an SQL Statement

Hi,

I need to find a way to include order by in 2 subquery in an SQL statement.

Basically, I have 2 differents statements that I need to order by differently but I need them in 1 SQL statement as I'm having a DTS that exports this statement into a fixed width file.

Statements are:

select * from table a
where date > 2007/01/01
order by date asc

select * from table b
where amount > 100.00
order by amount desc

------

this will not work....

select * from
(select * from table a
where date > 2007/01/01
order by date asc
union
select * from table b
where amount > 100.00
order by amount desc) c

Any thoughts on how to do this?
MetDiaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
select * from
(select a.*, convert(varchar(10), date, 120) as order_value_1, 0 as order_value_2 from table a
where date > 2007/01/01
union all
select b.*, null , amount from table b
where amount > 100.00
) c
order by order_value_1, order_value_2 desc
0
 
dqmqCommented:
select * from
(
select *, 1 as grp, row_number() (order by date asc) as seq from table a
where date > 2007/01/01
union
select *, 2 as grp,  row_number() (order by amount desc) as seq from table b
where amount > 100.00 ) v
order by v.grp, v.seq

0
 
LowfatspreadCommented:
select (columnlist without origin)
  from
(select 1 as origin,a.* from table a
where date > '20070101'
union
select 2 as origin ,b. * from table b
where amount > 100.00
) as c
order by case origin when 1 then 0
                                when 2 then case when amount > 0.00 then 2 else 3 end
                                end  
           ,case origin when 1 then convert(char(26),date,113)
                                else right("00000000000000000000000000"+convert(varchar(26), amount * -1),26)
                               end ASC
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
MetDiaAuthor Commented:
Hi everyone,

I tried your solutions and none is working. I forgot to mention that i'm using microsoft sql server and its TSQL.

Please any help will be appreciated

Thanks
0
 
dqmqCommented:
When it doesn't work, please tell us the error message. Try:

select * from
(
select *, 1 as grp, row_number() (order by date asc) as seq from table a
where date > '01/01/2007'  --possibly you mean >=
union
select *, 2 as grp,  row_number() (order by amount desc) as seq from table b
where amount > 100.00 ) v
order by v.grp, v.seq


0
 
MetDiaAuthor Commented:
Sorry about that...
Here is the message error:
Server: Msg 195, Level 15, State 10, Line 3
'row_number' is not a recognized function name.
Server: Msg 195, Level 15, State 1, Line 6
'row_number' is not a recognized function name.

So what should i do ?
0
 
dqmqCommented:
I guess you posted in the SQL Server 2005 forum by mistake.  In prior versions try this:

select * from
(
select *, 1 as grp,
(select count(*) from table a1 where a1.date < a.date) as seq
from table a
where a.date > '01/01/2007'  --possibly you mean >=
union
select *, 2 as grp,
(select count(*) from table b1 where b1.amount < b.amount) as seq
from table b
where b.amount > 100.00 ) v
order by v.grp, v.seq


0
All Courses

From novice to tech pro — start learning today.