asked on

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?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dqmq

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

Lowfatspread

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

MetDia

ASKER

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

dqmq

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

MetDia

ASKER

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 ?

dqmq

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