Thank Igor
Again...your solution works a treat...
Re my confusion...please contrast these two which both work
i.e.
order by (f.nexttime is null or f.nexttime=''), coalesce(f.nexttime, ''),
f.lastdate
vs
order by (case
when nexttime is not null then 0
when lasttime is not null then 1
else 2 end), nexttime, lasttime
You are of course correct re my requirement
i.e. Allow me to quote: "order results firstly by earlest *non-null/empty
string* next time"
Given this I fully understand Ordering by that which I require i.e.
non-null...non empty string and the latter of the above does precisely that
i.e. when nexttime is not null then 0... and... when lasttime is not null
BY CONTRAST the first query SEEMS TO MY NAIVE EYE to contradict my
requirement of not null i.e.
ORDER BY f.nexttime IS NULL
I HOPE THIS EXPLAINS MY CONFUSION
AS STATED BOTH WORK
IT'S JUST THAT THE FIRST ONE SEEMS COUNTER INTUITIVE
AND I'D LIKE TO UNDERSTAND THIS
BTW NULL = CHR$(0) vs '' = '' YES???
On 19 December 2012 13:42, Igor Tandetnik <i...@tandetnik.org> wrote:
> e-mail mgbg25171 <mgbg25...@blueyonder.co.uk> wrote:
> >> order by (f.nexttime is null or f.nexttime=''), coalesce(f.nexttime,
> ''),f.lastdate
> >
> > This worked fine re making sure that non-null nexttimes come BEFORE null
> > nexttimes.
> >
> > How would I extend this so that AFTER non-null nexttimes I get NON-NULL
> > lasttimes and then...
> > null nexttimes and lastimes in any order
>
> order by (case
> when nexttime is not null then 0
> when lasttime is not null then 1
> else 2 end), nexttime, lasttime
>
> > Also can I ask why you are ordering by nextime is null/'' at the
> > beginning...
>
> Because that's what you asked for. Allow me to quote: "order results
> firstly by earlest *non-null/empty string* next time" (emphasis mine). You
> do realize that NULL and empty string are two distinct values, right?
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
