Can I do this 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 2005 Forums

Transact-SQL (2005)

Can I do this ORDER BY

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-02-20 : 05:33:53

I've got this

SELECT forename,surname,tradingname FROM tblCustomers ORDER BY tradingname,forename

It gives these results

forename surname tradingname
Ben Dikens NULL
Emma Parker NULL
NULL NULL AFD Software

If its possible I want to group tradingname and forename together for the purposes of the order by - I think if it ignored nulls it would work, but how can I do that?

This is the result I want

forename surname tradingname
NULL NULL AFD Software
Ben Dikens NULL
Emma Parker NULL

Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 05:35:47

use order by forename

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-02-20 : 05:42:01

But then the trading name values are not in order

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 05:44:28

can u show me the output when u using the order by forename

r try this order by forename,tradingname

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-20 : 05:51:59

try this

declare @temp table ( forename varchar(30),surname varchar(30),tradingname varchar(30))
insert into @temp
select 'Ben', 'Dikens', NULL union all
select 'Emma', 'Parker', NULL union all
select NULL, NULL, 'AFD Software'

select * from @temp
order by case when forename is null or surname is null then 0 else 1 end

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-02-20 : 06:17:28

This fixed it

order by case when forename is null then tradingname else forename end

Thank you

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-20 : 06:21:16

Welcome...

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 06:24:44

[code]order by Case when coalesce(forename,surname) is null then tradingname Else forename End[/code]