Re: [sqlite] trying to exclude records which have a field that is null

[Keith Medcalf]

> 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

Re: [sqlite] select max(x), y from table

[Igor Tandetnik]

On 2/2/2013 6:46 PM, Gabor Grothendieck wrote:

In 3.7.11 there was a change to support the feature in the subject
which refers to guaranteeing that y comes from the same row having
maximum x.. See:

http://pages.citebite.com/o9y9n0p9neyt

Did this or other change also enhance the having clause to add a
feature to support a query containing "having max(...)" such as the
query here:

http://r.789695.n4.nabble.com/Filter-according-to-the-latest-data-tp4657248p4657305.html


The query in that thread is of the form "select * from MyTable group by 
Name having max(Timestamp)", and the expectation, somehow, is that the 
HAVING clause would cause each group to be represented by a row for 
which max(Timestamp) is reached. I'm not sure where this expectation 
comes from. This is a valid SQL statement whose HAVING clause means 
"only include a group in the resultset if max(Timestamp) for this group 
is logically true" (that is, not NULL, 0, empty string or empty blob).


A semantic change of the nature you envision is not backward compatible 
- it modifies the meaning of existing valid statements. Also, I'm pretty 
sure it's not supported by any SQL standard; and I'm not aware of any 
DBMS that would interpret the statement the way you want (which doesn't 
mean none such exists, of course).


All in all, It seems unlikely that such a proposal would be entertained.
--
Igor Tandetnik

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

Re: [sqlite] trying to exclude records which have a field that is null

[Petite Abeille]

On Feb 3, 2013, at 12:19 AM, e-mail mgbg25171  
wrote:

> "having by_or_on is not null " & _<==THIS ISN'T DOING IT FOR ME
> AND I'D LIKE TO KNOW WHY

Use a where clause

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

Re: [sqlite] trying to exclude records which have a field that is null

[Keith Medcalf]

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] Double quotes in query

[Klaas V]

>Mohit wrote:
>Can you use double quotes in the query?

In this case no problem, double quotes are ignored around a tbl_name

sqlite> create temp table "db..test"(c1 integer, c2);
sqlite> insert into "db..test" values (1, 'sqlite_temp_master');
sqlite> insert into "db..test" values (2, 'db..test');
sqlite> .width -8 20
sqlite> select * from "db..test";
      c1  c2                  
  
       1  sqlite_temp_master  
       2  db..test            
sqlite> select rootpage, type from "sqlite_temp_master" where tbl_name = 
'db..test';     
rootpage  type                
  
       2  table               





 

Cordiali saluti/Vriendelijke groeten/Kind regards,

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

[sqlite] trying to exclude records which have a field that is null

[e-mail mgbg25171]

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

Re: [sqlite] PRAGMA table_info documentation

[Petite Abeille]

On Feb 2, 2013, at 7:32 PM, chojra...@gmail.com wrote:

> I'd like to ask about contents of last column in result of PRAGMA
> table_info in SQLite 3.7.15.1 because it is not documented (
> http://www.sqlite.org/pragma.html#pragma_table_info).
> It looks as if the value is '1', the column is in the primary key, but I'm
> not quite sure.

If , in command shell, you set .head on, then you will see:

sqlite> .head on
sqlite> pragma table_info( 'foo' );

cid|name|type|notnull|dflt_value|pk

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