Re: [sqlite] ordering result sets

[e-mail mgbg25171]

Igor
>case when f.nexttime IS NOT NULL then 0 else 1 end
explains it very well and I see that your alternative is indeed an elegant
shortcut

Additionally...
thank you for putting me straight re NULL in SQL
I didn;t appreciate that

Your help is very much appreciated
Dean


On 19 December 2012 19:19, Igor Tandetnik  wrote:

> case when f.nexttime IS NOT NULL then 0 else 1 end
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordering result sets

[Igor Tandetnik]

On 12/19/2012 10:07 AM, e-mail mgbg25171 wrote:

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


The expression (f.nexttime IS NULL) evaluates to 0 (which represents 
"false") when f.nexttime is in fact *not* NULL, and to 1 (true) when it 
is in fact NULL. In other words, it's a shorthand for


case when f.nexttime IS NULL then 1 else 0 end

or equivalently

case when f.nexttime IS NOT NULL then 0 else 1 end


BTW NULL = CHR$(0) vs '' = '' YES???


No. NULL is NULL - it doesn't compare equal to anything, not even to 
itself. For details, see http://en.wikipedia.org/wiki/Null_(SQL)

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordering result sets

[e-mail mgbg25171]

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  wrote:

> e-mail mgbg25171  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

Re: [sqlite] ordering result sets

[Igor Tandetnik]

e-mail mgbg25171  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

Re: [sqlite] ordering result sets

[e-mail mgbg25171]

Igor
>I'm not sure I completely understand
I'm not surprised re-reading my requirement. It isn't clear at all.
I'll have a play with what you've given me first.
Thank you very much...not only for the solution but also for your
explanation which I really appreciate.
Dean

On 17 December 2012 00:39, Igor Tandetnik  wrote:

> e-mail mgbg25171  wrote:
> > I have modified my program to have next time (not shown) as well as next
> > date
> > What I'd to know is...
> > How do I order results firstly by earlest non-null/empty string next time
> > (ALL DAYS HERE WILL BE TODAY) order and then BY earliest non-null/empty
> > string nextdate order
> > so...
> > I end up with a list of records in call time order followed by a list of
> > records beginning with the one called longest ago.
>
> I'm not sure I completely understand the requirement, but play with
> something like this:
>
> order by (f.nexttime is null or f.nexttime=''), coalesce(f.nexttime, ''),
> f.lastdate
>
> The first expression is boolean, its result is 0 or 1, so what it does is
> separate all records into two groups, and sort the first one (the one for
> which the expression is false) ahead of the second one (where the
> expression is true). The other two expressions are each designed to sort
> one of these groups without affecting the other.
> --
> 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

Re: [sqlite] ordering result sets

[Igor Tandetnik]

e-mail mgbg25171  wrote:
> I have modified my program to have next time (not shown) as well as next
> date
> What I'd to know is...
> How do I order results firstly by earlest non-null/empty string next time
> (ALL DAYS HERE WILL BE TODAY) order and then BY earliest non-null/empty
> string nextdate order
> so...
> I end up with a list of records in call time order followed by a list of
> records beginning with the one called longest ago.

I'm not sure I completely understand the requirement, but play with something 
like this:

order by (f.nexttime is null or f.nexttime=''), coalesce(f.nexttime, ''), 
f.lastdate

The first expression is boolean, its result is 0 or 1, so what it does is 
separate all records into two groups, and sort the first one (the one for which 
the expression is false) ahead of the second one (where the expression is 
true). The other two expressions are each designed to sort one of these groups 
without affecting the other.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users