May 23, 2002 at 8:53 am
Hi all,
Somehow I think this may be a newbie question, but despite the risk of getting flamed I have to pose it as I just can't find a solution. Consider the following:
SELECT field1, field2 FROM table order by field3
Field3 is a varchar and may contain NULL, empty string or a value. The behavior I'm getting orders NULLs before actual values - I want to change this behavior such that actual values proceed NULLS/empty strings in an ASC ORDER BY. Any ideas?
TIA,
David Stott
May 23, 2002 at 9:27 am
Try :-
SELECT field1, field2 FROM table
order by
case
when field3 is null then 1
when field3 = '' then 1
else 0
end
,field3
Andy Jones
.
May 23, 2002 at 9:30 am
Try:
SELECT field1, field2 FROM table
order by isnull(nullif(field3,''),'zzz') asc
May 23, 2002 at 10:32 am
Or
order by
case
when nullif(field3,'') is null then 1
else 0
end
,field3
To combine shrink code needs, the only problem I see with epols idea is zzz is last yes but zzza would be after that. Logicall should not happen but it is better to use values that are guarenteed to return what you want.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 23, 2002 at 10:41 am
quote:
the only problem I see with epols idea is zzz is last yes but zzza would be after that. Logicall should not happen but it is better to use values that are guarenteed to return what you want.
Put more "zzzzzzzzzzz"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply