By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,692 Members | 1,843 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,692 IT Pros & Developers. It's quick & easy.

sorting with null

P: 1
I have three fields firstname,lastname,company

I need to be able to sort by company,lastname , but I dont want all the NULL values displayed first. This is what I would like to do. Can I accomplish this in my SELECT?

lastname~ company
Anicker~Advanced Micro
Beck~ADR Services
Abbot~NULL
Abid~NULL
Stephens~Academy of art
Sbarge~Adams Co
Anchor~~NULL


Thanks,Adrian
Jan 31 '08 #1
Share this Question
Share on Google+
2 Replies


deepuv04
Expert 100+
P: 227
I have three fields firstname,lastname,company

I need to be able to sort by company,lastname , but I dont want all the NULL values displayed first. This is what I would like to do. Can I accomplish this in my SELECT?

lastname~ company
Anicker~Advanced Micro
Beck~ADR Services
Abbot~NULL
Abid~NULL
Stephens~Academy of art
Sbarge~Adams Co
Anchor~~NULL


Thanks,Adrian
the null values always come on the top, if you dont want to display null vaues on the top wat we can do is display empty string if the last name is null

select col1,col2,isnull(lastname,'') as lastname from tablename order by lastname

thanks
Jan 31 '08 #2

ck9663
Expert 2.5K+
P: 2,878
here's a long one...

select lastname, company, '1' as sortorder from yourtable where company is not null
union all
select lastname, company, '2' as sortorder from yourtable where company is null
order by 3, 1, 2

-- ck
Jan 31 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.