> Mayhaps you mean:
>
> Select f.*
> from firms f
> left join (select firm_id, max(by_or_on) as boo
> from calls
> group by firm_id
> having by_or_on is not null
> order by by_or_on desc) c
> on c.firm_id = f.id
> order by boo desc;
Should be:
Select f.*
from firms f
left join (select firm_id, max(by_or_on) as boo
from calls
group by firm_id
having max(by_or_on) is not null
order by max(by_or_on) desc) c
on c.firm_id = f.id
order by boo desc;
or the equivalent:
Select f.*
from firms f
left join (select firm_id, max(by_or_on) as boo
from calls
group by firm_id
having boo is not null
order by boo desc) c
on c.firm_id = f.id
order by boo desc;
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Keith Medcalf
> Sent: Saturday, 02 February, 2013 17:11
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] trying to exclude records which have a field that is
> null
>
>
> I'm surprised you are getting anything at all since the statement is
> semantically invalid.
>
> Select f.*
> from firms f
> left join (select firm_id, max(by_or_on) as boo
> from calls
> group by firm_id
> having by_or_on is not null
> order by by_or_on desc)
> on c.firm_id = f.id
> order by boo desc;
>
> Perhaps you can describe the result you are attempting to obtain.
>
> **the having clause filters the "return rows" of a "group by" (aggregate)
> select. Therefore the references in the HAVING can only test against
> "returned columns", not against the "source columns" from which the result
> is derived. Once uses the WHERE clause to apply conditions to the input
> of the aggregate, and HAVING to apply conditions to the output.
>
> **the same applies to the "order by" of a "group by" (aggregate) select.
> You can only order by the "returned result columns", not by the "source
> data columns".
>
> Mayhaps you mean:
>
> Select f.*
> from firms f
> left join (select firm_id, max(by_or_on) as boo
> from calls
> group by firm_id
> having by_or_on is not null
> order by by_or_on desc) c
> on c.firm_id = f.id
> order by boo desc;
>
> of course, the order by inside the table subquery is useless, so this
> would become:
>
> Select f.*
> from firms f
> left join (select firm_id, max(by_or_on) as boo
> from calls
> group by firm_id
> having boo is not null) c
> on c.firm_id = f.id
> order by boo desc;
>
> but perhaps you really mean:
>
> Select f.*
> from firms f
> left join (select firm_id, max(by_or_on) as boo
> from calls
> where by_or_on is not null
> group by firm_id) c
> on c.firm_id = f.id
> order by boo desc;
>
> which is a somewhat different thing.
>
> ---
> () ascii ribbon campaign against html e-mail
> /\ www.asciiribbon.org
>
>
> > -----Original Message-----
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of e-mail mgbg25171
> > Sent: Saturday, 02 February, 2013 16:19
> > To: General Discussion of SQLite Database
> > Subject: [sqlite] trying to exclude records which have a field that is
> > null
> >
> > wSQL = _
> > "Select f.* " & _
> > "from firms f " & _
> > "left join " & _
> > "(" & _
> > "select firm_id, max(by_or_on) as boo " & _
> > "from calls " & _
> > "group by firm_id " & _
> > "having by_or_on is not null " & _ <==========THIS ISN'T DOING IT FOR
> > ME
> > AND I'D LIKE TO KNOW WHY
> > "order by by_or_on desc" & _
> > ") c " & _
> > "on c.firm_id = f.id " & _
> > "order by boo desc;"
> >
> > Any help much appreciated
> > _______________________________________________
> > 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
