How to use UNION and 'order by' together? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Database Design and Application Architecture

How to use UNION and 'order by' together?

Author

Topic

sel129
Starting Member

9 Posts

Posted - 2007-09-28 : 00:10:02

I wanted to use UNION and 'order by' in the same script but it keeps giving me error

eg;

select name,date
where name = 'sel'
union
select name,date
where name = 'ali'
order by name

I don't know where to put the 'order by'

Can anybody help me?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-28 : 00:16:20

quote:
Originally posted by sel129

I wanted to use UNION and 'order by' in the same script but it keeps giving me error

eg;

select name,date
where name = 'sel'
union
select name,date
where name = 'ali'
order by name

I don't know where to put the 'order by'

Can anybody help me?

Your query looks fine. Are you getting any error ?

KH
[spoiler]Time is always against us[/spoiler]

sel129
Starting Member

9 Posts

Posted - 2007-09-28 : 00:26:00

Yes i'm getting error msg...

error: invalid column name <-- order by name (its says name is an invalid column name)

my scrip is actually more complicated since i'm using 4 tables..

when i remove the 'order by' script it works fine...

i even tried putting the 'order by' before union also gives me error

i just want to know how and where to put the 'order by' if using UNION

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-28 : 00:30:29

you can't put the order by before the union. It as to be at the last statement

alternatively you can also do this


select name, date
from
(
    select name, date from table1
    union
    select name, date from table2
    union 
    select name, date from table3
) a
order by name

KH
[spoiler]Time is always against us[/spoiler]

sel129
Starting Member

9 Posts

Posted - 2007-09-28 : 00:44:47

quote:
Originally posted by khtan

you can't put the order by before the union. It as to be at the last statement

alternatively you can also do this

select name, date
from
(
select name, date from table1
union
select name, date from table2
union
select name, date from table3
) a
order by name

KH
[spoiler]Time is always against us[/spoiler]

what is 'a' after the bracket?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-28 : 00:51:17

alias name for the derived table

KH
[spoiler]Time is always against us[/spoiler]

sel129
Starting Member

9 Posts

Posted - 2007-09-28 : 01:25:54

quote:
Originally posted by khtan

alias name for the derived table

KH
[spoiler]Time is always against us[/spoiler]

i thought 'a' was a typo..

WOW!! it works!! u're a genius!!!

Thx KH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 02:17:13

quote:
Originally posted by sel129

I wanted to use UNION and 'order by' in the same script but it keeps giving me error

eg;

select name,date
where name = 'sel'
union
select name,date
where name = 'ali'
order by name

I don't know where to put the 'order by'

Can anybody help me?

It is becuase there is no FROM table_name in the query

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-28 : 14:38:09

quote:
Originally posted by sel129
WOW!! it works!! u're a genius!!!

HEY!

There will be no name calling on this board

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-09-28 : 14:49:22

"There will be no name calling on this board"

You're just trying to give us all a number

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2007-09-28 : 21:03:04

An ORDER BY needs column names. The result of a UNION has no column names until you alias that resul with a table and column names. Likewise for EXCEPT and INTERSECT

--CELKO--
Joe Celko, SQL Guru

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources