Simple Name order - 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 2008 Forums

Transact-SQL (2008)

Simple Name order

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2010-03-29 : 06:41:00

Msg 145, Level 15, State 1, Line 2
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I select forename = ' ' + surname as Name

and get this error

I want to
order by a, b, c, surname

showing the column as Name, ordered by surname

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-29 : 06:50:47

order by a, b, c, ' ' + surname

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-29 : 07:16:31

Order By will know dervided column name

So, use

order by a, b, c, Name

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-29 : 08:28:10

quote:
Originally posted by madhivanan

Order By will know dervided column name

So, use

order by a, b, c, Name

Madhivanan

Failing to plan is Planning to fail

Not when DISTINCT is specified, I guess.

Harsh Athalye
http://www.letsgeek.net/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-29 : 08:39:36

select distinct data,data+1 as data1 from (select 123 as data) as t
order by data1

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-29 : 08:46:14

somehow that doesn't work for multiple columns in Distinct clause:


select distinct data1,data2+1 as data1 from (select 123 as data1, 456 as data2) as t
order by data1, data2

Harsh Athalye
http://www.letsgeek.net/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-29 : 08:55:34

quote:
Originally posted by harsh_athalye

somehow that doesn't work for multiple columns in Distinct clause:

select distinct data1,data2+1 as data1 from (select 123 as data1, 456 as data2) as t
order by data1, data2

Harsh Athalye
http://www.letsgeek.net/

Now you have two similar column names which wont work

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-29 : 09:06:34

My bad. That was typo. I guess sql server gives that error when the computed columns are not given alias.

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources