union order by - 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

SQL Server 2000 Forums

Transact-SQL (2000)

union order by

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-11-20 : 11:50:15

Is there a way to give the 3rd column an alias and then order it?
Doesn't seem to work:

select inv_no AS 'Invoice Number',
actiontaken AS 'History',
actiondate AS 'Recorded Date'
from invoicehistory where inv_no = '0090951830'

union ALL

select invno AS 'Invoice Number',
note AS 'History',
notedate AS 'Recorded Date'
from invoicenotes where invno = '0090951830'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-20 : 11:53:15

[code]select *
from
(
select inv_no AS 'Invoice Number',
actiontaken AS 'History',
actiondate AS 'Recorded Date'
from invoicehistory where inv_no = '0090951830'

union ALL

select invno AS 'Invoice Number',
note AS 'History',
notedate AS 'Recorded Date'
from invoicenotes where invno = '0090951830'
) a
order by [Recorded Date][/code]

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

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-11-20 : 11:58:20

Server: Msg 8626, Level 16, State 1, Line 4
Only text pointers are allowed in work tables, never text, ntext, or image columns. The query processor produced a query plan that required a text, ntext, or image column in a work table.

?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-20 : 12:05:21

oh column note is text ? then insert into a temp table and select back from the temp table with ORDER BY

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-20 : 14:10:00

Don't put single quotes around your column names, it is too confusing and it looks like string literals.

i.e.,don't write:

select x as 'columnname'

instead, write:

select x as columnname

If you have a space in there, use [] :

select x as [column name]

- Jeff
http://weblogs.sqlteam.com/JeffS

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-20 : 14:48:41

Even if you don't have a space you can use the square parens ;)
[CODE]
SELECT 1 As [colmnName]
, 2 As [column name]
[/CODE]
Consistency is the key, right? ;)

George
<3Engaged!

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-11-21 : 04:58:28

out of interest, why can't I order by if the note column is text?
i.e. why does it have to go in a temp table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources