Ordering custom columns - 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 2005 Forums

Transact-SQL (2005)

Ordering custom columns

Author

Topic

nobelins
Starting Member

8 Posts

Posted - 2009-10-08 : 15:27:37

Hi,

I have a query like this;

SELECT COMPANY_NAME,ADDRESS,ZIPCODE,
CASE WHEN COM_TYPE = 0 THEN 'Seller'
WHEN COM_TYPE = 1 THEN 'Buyer' AS COMPANY_TYPE
FROM COMPANIES

My question is, how can i order the rows according to COMPANY_TYPE. When i use "ORDER BY COMPANY_TYPE ASC" sql server says there is no column in the table with this name! It's logical couse i created it in the query. Is there a way to sort these type of columns with t-sql?

thanks

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-10-08 : 15:39:48

Try:

CASE WHEN COM_TYPE = 0 THEN 'Seller'
WHEN COM_TYPE = 1 THEN 'Buyer' END AS COMPANY_TYPE

otherwise, which Version are you running? Or Compatibility Level?
Or is the error refering to 'com_Type'?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-08 : 16:14:38

EDIT : Removed the statement.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-10-08 : 16:22:13

quote:
Originally posted by vijayisonly

use a CASE statement in the ORDER BY too...

ORDER BY
CASE WHEN COM_TYPE = 0 THEN 1
WHEN COM_TYPE = 1 THEN 0
END

I didn't need to in 2005. Does this depend on the version?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-08 : 16:27:57

Oops..you are right!!!

Not sure why I gave that...It should work that way!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources