order null and '' to the last position
Hi,
when I order a table by a specific attribute, I am supposed to put all ''
and NULL values to the last position. Since I can not alter the tables and
both input values (NULL and empty string) can appear, I came up with the
following solutions.
1.
select distinct decode(restsuesse,
'','zzzz',
NULL,'zzzz',
restsuesse) as restsuesse
from artikel
order by restsuesse
2.
select distinct
case restsuesse
when '' then 'zzzz'
when null then 'zzzz'
else restsuesse
end
as restsuesse
from artikel order by restsuesse
Which of these is preferable (performance) or should both be dropped in
favour of a third solution?
Regards,
Michael
On Wed, 13 Aug 2003 12:45:36 +0200, "michael wimmer"
<m.wimmer@ecom-it.at> wrote:
>Hi,
>
>when I order a table by a specific attribute, I am supposed to put all ''
>and NULL values to the last position. Since I can not alter the tables and
>both input values (NULL and empty string) can appear, I came up with the
>following solutions.
>
>1.
>select distinct decode(restsuesse,
> '','zzzz',
> NULL,'zzzz',
> restsuesse) as restsuesse
>from artikel
>order by restsuesse>
>2.
>select distinct
> case restsuesse
> when '' then 'zzzz'
> when null then 'zzzz'
> else restsuesse
> end
> as restsuesse
>from artikel order by restsuesse>
>Which of these is preferable (performance) or should both be dropped in
>favour of a third solution?
>
What does the explain plan say?
What is the difference in response time?
"John Carlson" <john_carlson@whsmithusa.com> schrieb im Newsbeitrag
news:c5fkjvoq5vbv8h697ogsacq145dosakbrr@4ax.com...
> On Wed, 13 Aug 2003 12:45:36 +0200, "michael wimmer"
> <m.wimmer@ecom-it.at> wrote:
>
> >Hi,
> >
> >when I order a table by a specific attribute, I am supposed to put all ''
> >and NULL values to the last position. Since I can not alter the tables
and
> >both input values (NULL and empty string) can appear, I came up with the
> >following solutions.
> >
> >1.
> >select distinct decode(restsuesse,
> > '','zzzz',
> > NULL,'zzzz',
> > restsuesse) as restsuesse
> >from artikel
> >order by restsuesse> >
> >2.
> >select distinct
> > case restsuesse
> > when '' then 'zzzz'
> > when null then 'zzzz'
> > else restsuesse
> > end
> > as restsuesse
> >from artikel order by restsuesse> >
> >Which of these is preferable (performance) or should both be dropped in
> >favour of a third solution?
> >
>
> What does the explain plan say?
ok, both give me the same estemated costs. this still leaves my last
question open, if there is a better solution for this?
regards,
michael
> What is the difference in response time?