Keith, Petite I'm really grateful for your assistance. I've tried your solutions and they don't quite give me what I want so... sorry for misleading you. Secondly your suggestion that I explain in words is a good one. Here goes...
I've got a table of FIRMS and a table of CALLS made to those firms. In the table of calls to the firms, two of the fields are unsurprisingly...FIRM_ID and a a field called BY_OR_ON that records the date by which something must be done. BY_OR_ON is mostly null. I want to FILTER the FIRMS records to EXCLUDE any which do not have a single calls record with a non-null by field I'm only interest in the LATEST/MAX BY_OR_ON date for each firm which will exclude many of the calls records. I then want to SORT the FIRMS records to order them by earlest...latest BY_OR_ON date for each firm, first... if earlies...latest BY_OR_ON makes sense. I hope this helps to explain Best Regards On 3 February 2013 03:39, Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
