SQL UNION with TOP and ORDER BY

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL UNION with TOP and ORDER BY

Author

Topic

nskatp
Starting Member

8 Posts

Posted - 2010-09-01 : 17:41:57

Hi I need some help with an UNION query to 3 different tables that must use TOP and ORDER BY, the order by is not working correctly unfortunately.

Table1:
time code

Table2:
time code field_a field_b

Table3:
time code field_c field_d

what I need is to show the top 10 rows of each table ordered by the time column and showing all columns in the 3 tables.

This is what I did

select top 10 code, time, '' as field_a, '' as field_b, '' as field_c, '' as field_d from Table1
union
select top 10 code, time, field_a as field_a, field_b as field_b, '' as field_c, '' as field_d from Table2
union
select top 10 code, time, '' as field_a, '' as field_b, field_c as field_c, field_d as field_d from Table3
order by time desc

but for information in table1 and table2 is not giving me the "latest" rows.

I appreciate any help.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-01 : 18:05:19

[code]
With t1 as (select top 10 code, time, '' as field_a, '' as field_b, '' as field_c, '' as field_d from Table1 order by time desc),
t2 as (select top 10 code, time, field_a as field_a, field_b as field_b, '' as field_c, '' as field_d from Table2 order by time desc),
t3 as (select top 10 code, time, '' as field_a, '' as field_b, field_c as field_c, field_d as field_d from Table3 order by time desc)
SELECT * FROM t1
UNION
SELECT * FROM t2
UNION
SELECT * FROM t3
ORDER BY time DESC[/code]

nskatp
Starting Member

8 Posts

Posted - 2010-09-01 : 18:42:50

Thanks, that's much better, working as needed.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-01 : 20:47:07

Glad to help

By the way, I just want to point out (and you may already know this) that UNION is like SELECT DISTINCT, while UNION ALL doesn't carry the additional overhead of the DISTINCT filter.

so if you don't need it, then use UNION ALL instead of UNION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources