sorting with null

[allroad02]

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

[deepuv04]

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

[ck9663]

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