UNION ALL and ORDER BYs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UNION ALL and ORDER BYs

Author

Topic

liptonIcedTea
Starting Member

26 Posts

Posted - 2008-02-27 : 18:59:37

Hi

I have two tables which I'm combining.

Select employeeID as id, startDt as startDate from Table1
union all
Select CustomerID as id, startDt as startDate from Table2
order by startDt

This works fine, unfortunately, my problem is I only want to display the first latest customer. In a Union All, how do i distinguish whether I'm ordering the table it is trying to union or the actual unioned table tiself?

For example, this does not work.

Select employeeID as id, startDt as startDate from Table1
union all
Select top 1 CustomerID as id, startDt as startDate from Table2
order by startDt desc
order by startDt

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-27 : 20:49:18

do it as a sub-query. Something like

Select employeeID as id, startDt as startDate from Table1
union all
select * from
(
Select top 1 CustomerID as id, startDt as startDate from Table2
order by startDt desc
)
order by startDt

liptonIcedTea
Starting Member

26 Posts

Posted - 2008-02-28 : 00:07:20

Thanks

Yes that's what i ended up doing.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-28 : 02:26:51

quote:
Originally posted by LoztInSpace

do it as a sub-query. Something like

Select employeeID as id, startDt as startDate from Table1
union all
select * from
(
Select top 1 CustomerID as id, startDt as startDate from Table2
order by startDt desc
) as t
order by startDt

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources