Re: [sqlite] SQL help

[Jose Isaias Cabrera]

True that. ;-)


From: sqlite-users  on behalf of 
x 
Sent: Tuesday, December 31, 2019 03:15 AM
To: SQLite mailing list 
Subject: Re: [sqlite] SQL help

LOL. English isnae oor furst language either. Ye ken whit a mean. 




From: sqlite-users  on behalf of 
Jose Isaias Cabrera 
Sent: Monday, December 30, 2019 9:50:22 PM
To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] SQL help



x, on Saturday, December 28, 2019 10:46 AM, wrote...

> Apologies if that’s not worded correctly. Scottish education could be
> done gooder 

Wrong English! You should have said, "Scottish education could have been done 
gooder."  Com'on! This is not even my first language! :-)

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

Re: [sqlite] SQL help

[x]

LOL. English isnae oor furst language either. Ye ken whit a mean. 




From: sqlite-users  on behalf of 
Jose Isaias Cabrera 
Sent: Monday, December 30, 2019 9:50:22 PM
To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] SQL help



x, on Saturday, December 28, 2019 10:46 AM, wrote...

> Apologies if that’s not worded correctly. Scottish education could be
> done gooder 

Wrong English! You should have said, "Scottish education could have been done 
gooder."  Com'on! This is not even my first language! :-)

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

Re: [sqlite] SQL help

[Jose Isaias Cabrera]

x, on Saturday, December 28, 2019 10:46 AM, wrote...

> Apologies if that’s not worded correctly. Scottish education could be
> done gooder 

Wrong English! You should have said, "Scottish education could have been done 
gooder."  Com'on! This is not even my first language! :-)

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

Re: [sqlite] SQL help

[x]

Thanks Barry. I think that’s saying something like what I was saying but I put 
it better than them. I’m buoyed by the fact it’s not just Scottish education 
that could be done gooder 


From: Barry Smith<mailto:smith.bar...@gmail.com>
Sent: 28 December 2019 22:49
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] SQL help

Why does section 6.4.3.a.i of that linked standard not apply to his sub select?

In the quote below, CR is Column Reference, CN is Column Name.

3) If CR does not contain a , then CR shall be contained
within the scope of one or more s or s whose associated tables include a column whose  is CN. Let the phrase possible qualifiers denote those
s and s.

a) Case:

  i) If the most local scope contains exactly one possible
 qualifier, then the qualifier Q equivalent to that unique
  or  is implicit.

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

Re: [sqlite] SQL help

[Simon Slavin]

On 28 Dec 2019, at 10:49pm, Barry Smith  wrote:

> Why does section 6.4.3.a.i of that linked standard not apply to his sub 
> select?

I was thinking of the matching on (3,3), but perhaps you are right.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help

[Barry Smith]

Why does section 6.4.3.a.i of that linked standard not apply to his sub select?

In the quote below, CR is Column Reference, CN is Column Name.

3) If CR does not contain a , then CR shall be contained
within the scope of one or more s or s whose associated tables include a column whose  is CN. Let the phrase possible qualifiers denote those
s and s.

a) Case:

  i) If the most local scope contains exactly one possible
 qualifier, then the qualifier Q equivalent to that unique
  or  is implicit.



> On 28 Dec 2019, at 9:48 am, Simon Slavin  wrote:
> 
> On 28 Dec 2019, at 5:19pm, Barry Smith  wrote:
> 
>> Is not identifier resolution (search in the current scope first then step 
>> out) part of the SQL standard?
> 
> The way the SELECT statements are nested in the question is itself 
> non-standard in any version of SQL I can cite in this message.
> 
>> Damn closed standards and their inability to check without paying through 
>> the nose.
> 
> For that reason, we quote the 180,000 word SQL-92 …
> 
> 
> 
> … quite a lot, and the nine parts of SQL:2016 never.  (Hmm.  No, there's no 
> SQL:2019 yet.)
> 
>> Even if not, and this is in fact undocumented, I would be amazed if it 
>> changed, purely for the sheer amount of software it would break.
> 
> 
> Oh, me too.  But I wouldn't intentionally write code that depended on it.  
> Nor would I recommend that to anyone else.  Every time a developer says "We 
> do  for backward compatibility." I die a little.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help

[Simon Slavin]

On 28 Dec 2019, at 5:19pm, Barry Smith  wrote:

> Is not identifier resolution (search in the current scope first then step 
> out) part of the SQL standard?

The way the SELECT statements are nested in the question is itself non-standard 
in any version of SQL I can cite in this message.

> Damn closed standards and their inability to check without paying through the 
> nose.

For that reason, we quote the 180,000 word SQL-92 …



… quite a lot, and the nine parts of SQL:2016 never.  (Hmm.  No, there's no 
SQL:2019 yet.)

> Even if not, and this is in fact undocumented, I would be amazed if it 
> changed, purely for the sheer amount of software it would break.


Oh, me too.  But I wouldn't intentionally write code that depended on it.  Nor 
would I recommend that to anyone else.  Every time a developer says "We do 
 for backward compatibility." I die a little.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help

[Barry Smith]

Is not identifier resolution (search in the current scope first then step out) 
part of the SQL standard? Damn closed standards and their inability to check 
without paying through the nose.

Even if not, and this is in fact undocumented, I would be amazed if it changed, 
purely for the sheer amount of software it would break.

> On 28 Dec 2019, at 8:19 am, Simon Slavin  wrote:
> 
> On 28 Dec 2019, at 3:46pm, x  wrote:
> 
>> In the embedded select it takes c from the outer table t1 but doesn’t flag 
>> ‘ambiguous column’ for b in the embedded select. Is it standard that tables 
>> at the same level are searched first for the column and tables from the 
>> surrounding  levels are only searched if it’s not found?
> 
> Undocumented.  Therefore even if someone told you the what's done now, it 
> might change in some future version of SQLite.  Either using a different 
> level, or deciding to report the ambiguity.
> 
> To make your code dependable, instead of "b" specify "t1.b" or "t2.b", as you 
> did in your WHERE clause.  This will ensure that your code is understood 
> correctly by SQLite, and it will also help anyone who has to read your code 
> in the future.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help

[x]

Thanks Simon.

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

Re: [sqlite] SQL help

[Simon Slavin]

On 28 Dec 2019, at 3:46pm, x  wrote:

> In the embedded select it takes c from the outer table t1 but doesn’t flag 
> ‘ambiguous column’ for b in the embedded select. Is it standard that tables 
> at the same level are searched first for the column and tables from the 
> surrounding  levels are only searched if it’s not found?

Undocumented.  Therefore even if someone told you the what's done now, it might 
change in some future version of SQLite.  Either using a different level, or 
deciding to report the ambiguity.

To make your code dependable, instead of "b" specify "t1.b" or "t2.b", as you 
did in your WHERE clause.  This will ensure that your code is understood 
correctly by SQLite, and it will also help anyone who has to read your code in 
the future.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL help

[x]

Consider the following

sqlite> create table t1(a int, b int, c int);
sqlite> create table t2(a int, b int);
sqlite> insert into t1 values (1, 2, 3);
sqlite> insert into t2 values (3, 4);
sqlite> select a,(select b, c from t2 where t2.a=t1.a)=(3,3) from t1;
1|

In the embedded select it takes c from the outer table t1 but doesn’t flag 
‘ambiguous column’ for b in the embedded select. Is it standard that tables at 
the same level are searched first for the column and tables from the 
surrounding  levels are only searched if it’s not found? If so, is this also 
the case if selects are embedded to several depths. E.g. depth 3 tables 
searched first, then depth 2  and finally lowest depth?

Apologies if that’s not worded correctly. Scottish education could be done 
gooder 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help: by-range aggregation

[R Smith]

On 2017/03/09 10:11 AM, Dominique Devienne wrote:

Nice! Thanks Ryan. --DD

PS: If we ever meet, your reasonably-priced beverage of choice is on me :)


You're welcome, and safe, since I'm teetotal - which means I totally 
only drink tea. :)


(Been this way since that fatal PUI incident - that's Programming under 
the Influence - but we are all Mavericks sometimes!)


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

Re: [sqlite] SQL help: by-range aggregation

[Dominique Devienne]

On Wed, Mar 8, 2017 at 6:38 PM, R Smith  wrote:

>
> On 2017/03/08 5:35 PM, Dominique Devienne wrote:
>
>>
>> Now I only need a CTE to dynamically generate the ranges,
>>
>
> Well, that seems like fun!
> If I may...
>

Nice! Thanks Ryan. --DD

PS: If we ever meet, your reasonably-priced beverage of choice is on me :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help: by-range aggregation

[Dominique Devienne]

On Wed, Mar 8, 2017 at 6:08 PM, Simon Slavin  wrote:

>
> On 8 Mar 2017, at 3:35pm, Dominique Devienne  wrote:
> >
> > Then the results where in alphabetical order of bucket names,
> > so I had to re-join on ranges to order by ranges.low.
>
> You should be able to just add the ORDER BY clause to the original JOIN:
>
> SELECT ranges.name, bytes_per_set.v
>  FROM bytes_per_set
>  JOIN ranges ON bytes_per_set.v >= ranges.low
> AND bytes_per_set.v < ranges.high
>  ORDER BY ranges.low


Thanks, but that doesn't work, because the later aggregation loses that
order.
And I also want to preserve "holes in the middle", so I need a left-join
somewhere,
and at the very end is the only I can make this work (not that there's not
a better way). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help: by-range aggregation

[R Smith]

On 2017/03/08 5:35 PM, Dominique Devienne wrote:


Now I only need a CTE to dynamically generate the ranges,


Well, that seems like fun!
If I may...

-- CTEs
   -- Units Table (B, KB, MB, etc.)
WITH UT(id, uStr, byteDiv) AS (
SELECT 0, 'B', 1
UNION ALL
SELECT UT.id+1,
   CASE UT.id WHEN 0 THEN 'KB' WHEN 1 THEN 'MB' WHEN 2 THEN 
'GB' WHEN 3 THEN 'TB' WHEN 4 THEN 'PB' END,

   UT.byteDiv * 1024
  FROM UT
 WHERE UT.id < 5

-- Range Table (define lows/highs)
), RT(id, low, high) AS (
SELECT 0, 0, 1024
UNION ALL
SELECT RT.id+1, RT.high, RT.high*8
  FROM RT
 WHERE RT.id < 16

   -- Conversion Values (Match low/high Ranges to Units)
), CV(rid, low, high, luStr, luDiv, huStr, huDiv) AS (
SELECT RT.id, RT.low, RT.high, ULo.uStr, ULo.byteDiv, UHi.uStr, 
UHi.byteDiv

  FROM RT
  LEFT JOIN UT AS ULo ON (ULo.byteDiv <= RT.low  AND 
(ULo.byteDiv*1024) > RT.low ) OR (ULo.id = 0 AND RT.id = 0)
  LEFT JOIN UT AS UHi ON (UHi.byteDiv <= RT.high AND 
(UHi.byteDiv*1024) > RT.high)

 WHERE UHi.id IS NOT NULL

   -- Range Table with Name vs. limits definitions
), range(name, low, high) AS (
SELECT '['|| (CV.low / CV.luDiv) || CV.luStr ||' - '|| (CV.high / 
CV.huDiv) || CV.huStr ||']', CV.low, CV.high

  FROM CV
)

SELECT *
  FROM range
;


  -- name |   low |  high
  --  | - | -
  -- [0B - 1KB]   | 0 |  1024
  -- [1KB - 8KB]  |  1024 |  8192
  -- [8KB - 64KB] |  8192 | 65536
  -- [64KB - 512KB]   | 65536 |524288
  -- [512KB - 4MB]|524288 |   4194304
  -- [4MB - 32MB] |   4194304 |  33554432
  -- [32MB - 256MB]   |  33554432 | 268435456
  -- [256MB - 2GB]| 268435456 | 2147483648
  -- [2GB - 16GB] |2147483648 | 17179869184
  -- [16GB - 128GB]   |   17179869184 | 137438953472
  -- [128GB - 1TB]|  137438953472 | 1099511627776
  -- [1TB - 8TB]  | 1099511627776 | 8796093022208
  -- [8TB - 64TB] | 8796093022208 | 70368744177664
  -- [64TB - 512TB]   |70368744177664 | 562949953421312
  -- [512TB - 4PB]|   562949953421312 | 4503599627370496
  -- [4PB - 32PB] |  4503599627370496 | 36028797018963968
  -- [32PB - 256PB]   | 36028797018963968 | 288230376151711744


(Hope the format survived e-mailing)
Cheers,
Ryan

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

Re: [sqlite] SQL help: by-range aggregation

[Simon Slavin]

On 8 Mar 2017, at 3:35pm, Dominique Devienne  wrote:
> 
> Then the results where in alphabetical order of bucket names,
> so I had to re-join on ranges to order by ranges.low.

You should be able to just add the ORDER BY clause to the original JOIN:

SELECT ranges.name, bytes_per_set.v
 FROM bytes_per_set
 JOIN ranges ON bytes_per_set.v >= ranges.low
AND bytes_per_set.v < ranges.high
 ORDER BY ranges.low

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

Re: [sqlite] SQL help: by-range aggregation

[Dominique Devienne]

On Wed, Mar 8, 2017 at 3:47 PM, Clemens Ladisch  wrote:

> Dominique Devienne wrote:
> > On Wed, Mar 8, 2017 at 3:16 PM, Hick Gunter  wrote:
> >> Alternatively create a "range" table, insert your defined ranges and
> >> join/subquery to the original query.
> >
> > That's the easy part [...]
> > but the join/subquery you mention is still leaving me scratching my
> head...
>
>   WITH u(v) AS (
> SELECT sum(s)
> FROM t
> GROUP BY p
>   )
>   SELECT ranges.name,
>  u.v
>   FROM u
>   JOIN ranges ON u.v >= ranges.low
>  AND u.v <  ranges.high;
>
> name   v
> -  --
> [0, 1KB)   904
> [8KB, 64KB)36000
> [64KB, 512KB)  370368
>

Thank you Clemens. I wasn't familiar with joins on range conditions like
this.

I needed count aggregates, so I moved the query into another CTE, and
grouped that.

Then the results where in alphabetical order of bucket names,
so I had to re-join on ranges to order by ranges.low.

Then I changed the join into a left-join to see all ranges, even empty ones.

And finally I trimmed empty ranges at the top and bottom, leaving
only the "middle" holes, as I think it's more natural that way.

Now I only need a CTE to dynamically generate the ranges,
but I'll call it a day and this will be for another time.

Here's my final query (on a slightly modified schema). --DD

with
bytes_per_set(v) as (
  select sum(byte_size)
from values
   group by parent, set
),
minmax(a, b) as (
  select min(v), max(v) from bytes_per_set
),
buckets(name, v) as (
select ranges.name, bytes_per_set.v
  from bytes_per_set
  join ranges ON bytes_per_set.v >= ranges.low
 AND bytes_per_set.v < ranges.high
),
distrib(b, c) as (
select name, count(*)
  from buckets
 group by 1
)
select ranges.name as "size range",
   distrib.c as '#sets'
  from ranges
  left join distrib on ranges.name = distrib.b
 where ranges.low  < (select b from minmax)
   and ranges.high > (select a from minmax)
 order by ranges.low
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help: by-range aggregation

[Simon Slavin]

On 8 Mar 2017, at 2:33pm, Dominique Devienne  wrote:

> Same constraints of the "thousand separator" one, this will be a view in a
> DB to be viewed by any SQLite client, so not allowed here. --DD

In that case, I think Hick’s solution is good.  Do it in two steps: construct a 
temporary table which includes the ranges (presumably using CASE), then 
interrogate that table.  (Then presumably drop that table.)

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

Re: [sqlite] SQL help: by-range aggregation

[Clemens Ladisch]

Dominique Devienne wrote:
> On Wed, Mar 8, 2017 at 3:16 PM, Hick Gunter  wrote:
>> Alternatively create a "range" table, insert your defined ranges and
>> join/subquery to the original query.
>
> That's the easy part [...]
> but the join/subquery you mention is still leaving me scratching my head...

  WITH u(v) AS (
SELECT sum(s)
FROM t
GROUP BY p
  )
  SELECT ranges.name,
 u.v
  FROM u
  JOIN ranges ON u.v >= ranges.low
 AND u.v <  ranges.high;

name   v
-  --
[0, 1KB)   904
[8KB, 64KB)36000
[64KB, 512KB)  370368


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help: by-range aggregation

[Dominique Devienne]

On Wed, Mar 8, 2017 at 3:16 PM, Hick Gunter  wrote:

> Alternatively create a "range" table, insert your defined ranges and
> join/subquery to the original query.
>
> Create table range (label text, from integer, to integer);
>

That's the easy part (and a CTE can probably generate it dynamically, and
even I could come up with that CTE):

create table ranges (name text, low integer, high integer)
insert into ranges values
('[0, 1KB)', 0, 1024),
('[1KB, 8KB)', 1024, 8*1024),
('[8KB, 64KB)', 8*1024, 8*8*1024),
('[64KB, 512KB)', 8*8*1024, 8*8*8*1024),
('[512KB, 4MB)', 8*8*8*1024, 8*8*8*8*1024),
('[4MB, 32MB)', 8*8*8*8*1024, 8*8*8*8*8*1024),
('[32MB, MAX)', 8*8*8*8*8*1024, 8*8*8*8*8*8*1024);

but the join/subquery you mention is still leaving me scratching my head...
--DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help: by-range aggregation

[Dominique Devienne]

On Wed, Mar 8, 2017 at 2:53 PM, Simon Slavin  wrote:

> On 8 Mar 2017, at 1:46pm, Dominique Devienne  wrote:
> > I'd like to aggregate the sum of the size per-parent, from 1KB, to each
> > range
> > from low to 8*low (i.e. [low, 8*low)), but w/o having to write my hand
> all
> > possible
> > combination, with extra points for formatting the range bounds in KB, MB,
> > GB as appropriate.
>
> Does your programming language allow you to implememnt a SQLite external
> function which takes a value and returns a string indicating which range
> its in ?
>
> This would allow you to implement triggers on INSERT and UPDATE which
> automatically set a 'category' field in the table.  Then you could filter
> on value, or use GROUP BY, or other things.
>

Same constraints of the "thousand separator" one, this will be a view in a
DB to be viewed by any SQLite client, so not allowed here. --DD

PS: will be allowed the day SQLite embeds a lightweight programming
language and allows defining "persistent" User Defined Functions in the DB
itself, in a sqlite_functions table for example :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help: by-range aggregation

[Hick Gunter]

Alternatively create a "range" table, insert your defined ranges and 
join/subquery to the original query.

Create table range (label text, from integer, to integer);

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Simon Slavin
Gesendet: Mittwoch, 08. März 2017 14:53
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] SQL help: by-range aggregation


On 8 Mar 2017, at 1:46pm, Dominique Devienne <ddevie...@gmail.com> wrote:

> I'd like to aggregate the sum of the size per-parent, from 1KB, to
> each range from low to 8*low (i.e. [low, 8*low)), but w/o having to
> write my hand all possible combination, with extra points for
> formatting the range bounds in KB, MB, GB as appropriate.

Does your programming language allow you to implememnt a SQLite external 
function which takes a value and returns a string indicating which range its in 
?

This would allow you to implement triggers on INSERT and UPDATE which 
automatically set a 'category' field in the table.  Then you could filter on 
value, or use GROUP BY, or other things.

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] SQL help: by-range aggregation

[Simon Slavin]

On 8 Mar 2017, at 1:46pm, Dominique Devienne  wrote:

> I'd like to aggregate the sum of the size per-parent, from 1KB, to each
> range
> from low to 8*low (i.e. [low, 8*low)), but w/o having to write my hand all
> possible
> combination, with extra points for formatting the range bounds in KB, MB,
> GB as appropriate.

Does your programming language allow you to implememnt a SQLite external 
function which takes a value and returns a string indicating which range its in 
?

This would allow you to implement triggers on INSERT and UPDATE which 
automatically set a 'category' field in the table.  Then you could filter on 
value, or use GROUP BY, or other things.

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

[sqlite] SQL help: by-range aggregation

[Dominique Devienne]

Given a table with a (parent, child) PK, which carries a size "data" column,
I'd like to aggregate the sum of the size per-parent, from 1KB, to each
range
from low to 8*low (i.e. [low, 8*low)), but w/o having to write my hand all
possible
combination, with extra points for formatting the range bounds in KB, MB,
GB as appropriate.

This is not too dissimilar to my recent question about the "thousand
separator",
but a bit more complex. Any help of formulating this query generically
(using a CTE?)
would be appreciated.

Thanks, --DD

C:\Users\ddevienne>sqlite3.18.0rc
SQLite version 3.18.0 2017-03-06 20:44:13
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select printf('%,d', 1001);
1,001
sqlite> select 'Thank you Richard';
Thank you Richard
sqlite> create table t (p, c, s integer, primary key (p, c));
sqlite> insert into t values
   ...> (1, 1, 4),
   ...> (1, 2, 900),
   ...> (2, 1, 12000),
   ...> (2, 2, 24000),
   ...> (3, 1, 123456),
   ...> (3, 2, 123456),
   ...> (3, 3, 123456);
sqlite> select p, count(*), sum(s) from t group by p order by 3 desc;
3|3|370368
2|2|36000
1|2|904
sqlite> with u(v) as (select sum(s) from t group by p)
   ...> select '[1B, 1KB)', count(*) from u where v < 1024
   ...> union all
   ...> select '[1KB, 8KB)', count(*) from u where 1024 <= v and v < 8*1024
   ...> union all
   ...> select '[8KB, 64KB)', count(*) from u where 8*1024 <= v and v <
8*8*1024
   ...> union all
   ...> select '[64KB, 512KB)', count(*) from u where 8*8*1024 <= v and v <
8*8*8*1024
   ...> ;
[1B, 1KB)|1
[1KB, 8KB)|0
[8KB, 64KB)|1
[64KB, 512KB)|1
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL help on preparing a report

[Simon Slavin]

On 31 Jul 2016, at 7:11pm, Ertan Küçükoğlu  wrote:

> I prepared
> an excel file with 3 sheets.

A few notes:

First, you cannot attach files to messages on this forum.  We do this on 
purpose because if we made it easy to post files people would just zip up their 
whole project, post it to the list, and expect us to make it work.  If you need 
us to see a file please put it on the web somewhere and post a pointer.

Second, we don't like doing people's homework.  It's possible that someone will 
bother to help you but you stand a better chance by explaining what you're 
having problems with.

I recommend that you read the documentation on expressions



and SQLite core functions



and then concentrate on asking us about specific calculations you need, 
explaining where you got stuck yourself.

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

[sqlite] SQL help on preparing a report

[Ertan Küçükoğlu]

Hello,

I am developing an application with SQLite. There is this report and my SQL
knowledge is not good enough to cope with it. I do not want to do all these
calculations by regular select statements and writing an application code.
Details;

I have below two tables in the same SQLite database file:
CREATE TABLE IF NOT EXISTS FIS(
  ID  Integer  NOT NULL PRIMARY KEY AUTOINCREMENT,
  UniqueIDChar(48) not null COLLATE NOCASE,
  EKU integer  not null check (EKU > 0),
  ZNo integer  not null check(ZNo > 0),
  FNo integer  not null check(FNo > 0),
  Tarih   date not null,
  Saattime not null,
  Zaman   datetime not null,
  Barkod  Char(10) COLLATE NOCASE NOT NULL,
  KdvOran Floatnot null check(KdvOran >= 0),
  Miktar  Floatnot null,
  Birim   Char(10) COLLATE NOCASE,
  OrjinalBirimFiyat   Floatnot null check(OrjinalBirimFiyat >= 0),  
  KartBirimFiyat  Floatnot null check(KartBirimFiyat >= 0),
  TeraziBirimFiyatFloatnot null check(TeraziBirimFiyat >= 0),
  IndirimliBirimFiyat Floatnot null check(IndirimliBirimFiyat >= 0),
  PromosyonFiyat  Floatnot null check(PromosyonFiyat >= 0),
  BirimFiyat  Floatnot null check(BirimFiyat >= 0),
  KDVTutarFloatnot null,
  Tutar   Floatnot null,
  Kasiyer Char(20) not null COLLATE NOCASE,
  OturumIDSmallInt not null,
  Vardiya SmallInt check(Vardiya > 0),
  VeresiyeKoduChar(20) COLLATE NOCASE,
  POSSeriNo   Char(50) not null COLLATE NOCASE,
  POSFisYazmaSuresi   integer  not null check(POSFisYazmaSuresi >= 0)
);

CREATE TABLE IF NOT EXISTS FISODEME(
  IDInteger NOT NULL PRIMARY KEY AUTOINCREMENT,
  EKU   integer not null check (EKU > 0),
  ZNo   integer not null check(ZNo > 0),
  FNo   integer not null check(FNo > 0),
  Tarih date not null,
  Saat  time not null,
  Zaman datetime not null,
  OdemeTuru Smallint not null check(OdemeTuru in (1, 2, 3)),
  KDVTutari Float not null check(KDVTutari >= 0),
  FisToplamifloat not null check(FisToplami >= 0),
  OdemeMiktari  float not null check(OdemeMiktari >= 0),
  ParaUstu  float not null check(ParaUstu >= 0),
  Banka Char(20) COLLATE NOCASE,
  VeresiyeKodu  Char(20) COLLATE NOCASE,
  POSSeriNo Char(50) not null COLLATE NOCASE
);

It is not easy for me to explain the report by written words. So, I prepared
an excel file with 3 sheets. First two contains raw data from above tables.
Last one is the report itself. Please ignore yellow marked columns in raw
data sheets.

I am quite open to add new table(s) for reporting purposes if that makes the
report possible/easier.

It doesn't need to be a single SQL. Running several SQL statements is not a
problem at all. However, I do not want to deal with 10s of SQL statements if
it can be done for example with only 3. It will be something as 10s of
statements If I am to deal with it using my own knowledge.

I appreciate any help.

Regards,
Ertan Küçükoğlu

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

[sqlite] SQL Help: Finding case insensitive matches

[Dominique Devienne]

On Fri, May 13, 2016 at 6:06 PM, Igor Tandetnik  wrote:
> On 5/13/2016 11:51 AM, Dominique Devienne wrote:
>> But it still returns both rows. Any idea on that part?
>
> Change "u1.user <> u2.user" to "u1.user < u2.user"

Seems obvious once someone clever shows you, yet isn't... Thanks Igor!

[sqlite] SQL Help: Finding case insensitive matches

[Dominique Devienne]

On Fri, May 13, 2016 at 5:27 PM, John McKown
 wrote:
>> > select group_concat(user) from os_users group by upper(user) having
>> count(*) > 1
>> foo,FOO
>>
>
> how about: SELECT group_concat(user,"|") FROM os_users GROUP BY
> upper(user) HAVING count(*) > 1

Thanks John. Sorry, I wasn't clear. When I wrote foo|FOO, I meant 2 columns,
i.e. col1 = "foo", col2 = "FOO", not the "foo|FOO" text value in a
single column. --DD

[sqlite] SQL Help: Finding case insensitive matches

[Dominique Devienne]

On Fri, May 13, 2016 at 5:23 PM, Clemens Ladisch  wrote:
> Dominique Devienne wrote:
>>  select u1.user, u2.user from os_users u1, os_users u2 where upper(u1.user) 
>> = upper(u2.user) and u1.user <> u2.user;
>> [...] but they are slow (almost 3s, with close to 4M steps, i.e. O(N^2))
>
> You can get O(N * log N) with a proper index, and avoiding the function:
>
>  CREATE INDEX u_nocase ON os_users(user COLLATE NOCASE);
>
>  SELECT u1.user, u2.user
>  FROM os_users u1
>  JOIN os_users u2 ON u1.user COLLATE NOCASE = u2.user COLLATE NOCASE
>  AND u1.user <> u2.user;

This works great, in 3.5ms, 1/2 the time of the group by + group_concat.
But it still returns both rows. Any idea on that part?

> With a recent enough SQLite, you can use an expression index without
> changing the queries: CREATE INDEX u_upper ON os_users(upper(user));

My SQLiteSpy version is too old, and latest is only at 3.8.11, so I'll
try it at the command line later.

Thanks for the help Clemens. --DD

[sqlite] SQL Help: Finding case insensitive matches

[Clemens Ladisch]

Dominique Devienne wrote:
> Imagine I have a single table containing a single text column, of user names.
> I'm trying to find user names which differ only by case.
>
>  select u1.user, u2.user from os_users u1, os_users u2 where upper(u1.user) = 
> upper(u2.user) and u1.user <> u2.user;
>  select user from os_users u where exists (select 1 from os_users u2 where 
> upper(u2.user) = upper(u.user) and u2.user <> u.user);
>
> I tried both queries above, but they are slow (almost 3s, with close
> to 4M steps, i.e. O(N^2))

You can get O(N * log N) with a proper index, and avoiding the function:

 CREATE INDEX u_nocase ON os_users(user COLLATE NOCASE);

 SELECT u1.user, u2.user
 FROM os_users u1
 JOIN os_users u2 ON u1.user COLLATE NOCASE = u2.user COLLATE NOCASE
 AND u1.user <> u2.user;

(Same with the other query.)

With a recent enough SQLite, you can use an expression index without
changing the queries:

 CREATE INDEX u_upper ON os_users(upper(user));


Regards,
Clemens

[sqlite] SQL Help: Finding case insensitive matches

[Dominique Devienne]

Imagine I have a single table containing a single text column, of user names.
I'm trying to find user names which differ only by case.

> select count(*) from os_users
1969

> select u1.user, u2.user from os_users u1, os_users u2 where upper(u1.user) = 
> upper(u2.user) and u1.user <> u2.user
foo|FOO
FOO|foo

> select user from os_users u where exists (select 1 from os_users u2 where 
> upper(u2.user) = upper(u.user) and u2.user <> u.user)
foo|FOO
FOO|foo

I tried both queries above, but they are slow (almost 3s, with close
to 4M steps, i.e. O(N^2)), and ideally I'd like a query returning only
one row. And that's w/ or w/o a PK or UNIQUE INDEX on the one column.

> select group_concat(user) from os_users group by upper(user) having count(*) 
> > 1
foo,FOO

I did manage the above, which returns in 7ms, and get me what I want,
but in aggregated form (thus needs reparsing). Is there a query that
can return just foo|FOO, i.e. the pairs of mixed-case matches
efficiently?

Thanks, --DD

[sqlite] SQL Help: Finding case insensitive matches

[Igor Tandetnik]

On 5/13/2016 11:51 AM, Dominique Devienne wrote:
> But it still returns both rows. Any idea on that part?

Change "u1.user <> u2.user" to "u1.user < u2.user"
-- 
Igor Tandetnik

[sqlite] SQL Help: Finding case insensitive matches

[John McKown]

On Fri, May 13, 2016 at 10:14 AM, Dominique Devienne 
wrote:

> Imagine I have a single table containing a single text column, of user
> names.
> I'm trying to find user names which differ only by case.
>
> > select count(*) from os_users
> 1969
>
> > select u1.user, u2.user from os_users u1, os_users u2 where
> upper(u1.user) = upper(u2.user) and u1.user <> u2.user
> foo|FOO
> FOO|foo
>
> > select user from os_users u where exists (select 1 from os_users u2
> where upper(u2.user) = upper(u.user) and u2.user <> u.user)
> foo|FOO
> FOO|foo
>
> I tried both queries above, but they are slow (almost 3s, with close
> to 4M steps, i.e. O(N^2)), and ideally I'd like a query returning only
> one row. And that's w/ or w/o a PK or UNIQUE INDEX on the one column.
>
> > select group_concat(user) from os_users group by upper(user) having
> count(*) > 1
> foo,FOO
>
> I did manage the above, which returns in 7ms, and get me what I want,
> but in aggregated form (thus needs reparsing). Is there a query that
> can return just foo|FOO, i.e. the pairs of mixed-case matches
> efficiently?
>

?how about: SELECT group_concat(user,"|") FROM os_users GROUP BY
upper(user) HAVING count(*) > 1
??



>
> Thanks, --DD
>


-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

Re: [sqlite] SQL Help

[Stephen Chrzanowski]

Maybe with a query or two extra, you can determine a temp table, then build
on that.  Do an initial distinct look up on the primary fields you want as
the fields in your temp table and create it, then do the required queries
to get the raw data into the temp table, then do the finalized query to get
the results you need.  I can't think of a single step method to get what
you want done.  Any time I've had to take (what I call) vertical data and
convert it to a horizontal format, I've always had to construct either an
array in memory or create another table.

On Mon, Feb 9, 2015 at 7:55 AM, R.Smith  wrote:

> I used to make Pivot-table-like reports in (what I thought was) the normal
> way:
>
> SELECT ProductID, sum(QtyMade) AS TotQty,
>(sum(CASE  Mth  WHEN  1  THEN  QtyMade  ELSE  0  END)) AS 'Jan Qty'
> ,(sum(CASE  Mth  WHEN  2  THEN  QtyMade  ELSE  0  END)) AS 'Feb Qty'
>   ,(sum(CASE  Mth  WHEN  3  THEN  QtyMade  ELSE  0  END)) AS 'Mar Qty'
> ...
>   ,(sum(CASE  Mth  WHEN 11 THEN  QtyMade  ELSE  0  END)) AS 'Nov Qty'
>   ,(sum(CASE  Mth  WHEN 12 THEN  QtyMade  ELSE  0  END)) AS 'Dec Qty'
> FROM ProductionHist
> WHERE Year=2014
> GROUP BY ProductID
> ORDER BY TotQty DESC;
>
> etc.
>

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

Re: [sqlite] SQL Help

[John McKown]

This looks interesting:
http://search.cpan.org/~bduggan/SQLite-VirtualTable-Pivot-0.02/lib/SQLite/VirtualTable/Pivot.pm
it is a bit old - 2009 . Other SQL dialects have this either built in or
via an "extension". If you're doing this in a program, I would likely just
to two SELECT statements. The first gets the "Group" values and constructs
the next SELECT based on it using what you showed as an example when the
column names are known.

On Mon, Feb 9, 2015 at 6:55 AM, R.Smith  wrote:

> I used to make Pivot-table-like reports in (what I thought was) the normal
> way:
>
> SELECT ProductID, sum(QtyMade) AS TotQty,
>(sum(CASE  Mth  WHEN  1  THEN  QtyMade  ELSE  0  END)) AS 'Jan Qty'
> ,(sum(CASE  Mth  WHEN  2  THEN  QtyMade  ELSE  0  END)) AS 'Feb Qty'
>   ,(sum(CASE  Mth  WHEN  3  THEN  QtyMade  ELSE  0  END)) AS 'Mar Qty'
> ...
>   ,(sum(CASE  Mth  WHEN 11 THEN  QtyMade  ELSE  0  END)) AS 'Nov Qty'
>   ,(sum(CASE  Mth  WHEN 12 THEN  QtyMade  ELSE  0  END)) AS 'Dec Qty'
> FROM ProductionHist
> WHERE Year=2014
> GROUP BY ProductID
> ORDER BY TotQty DESC;
>
> etc.
>
> Which of course works fine if you have a predeterminable set of columns to
> extract, such as Months in the above case. How can I do this sort of thing
> (i'm only interested in similar results, the method is not important) for
> an indetermintate set of columns?
>
>Example data:
>ID  |  Product |  Group|  Qty
>+--++---
>1 |  ABC001  |A   |  10
>2 |  ABC001  |A   |  20
>   3 |  ABC002  |A   |  10
>4 |  ABC001  |B   |  10
>5 |  ABC002  |B   |  20
>6 |  ABC001  |F   |  60
>...
>  97 |  ABC001  |n   |  20
>  98 |  ABC002  |n   |  30
>
>To produce a result like:
>
>Product |  Tot Group A  |  Tot Group B  | ... | Tot Group n
>+---+---+-+-
>ABC001  |   30  |  10   | ... | 20
>ABC002  |   10  |  20   | ... | 30
>
>
> Both Products and Groups are indeterminate.  I toyed with the idea of
> making a temporary table or CTE with DISTINCT to contain only unique values
> for one set (either Procucts or Groups in the above example) and then maybe
> derive another table and finally a "SELECT * " query which would simply
> reproduce all, but of course that won't work - somewhere along the line you
> need a handle on the number of output columns.
>
> I know this is easy in a program or in a spreadsheet, but I am trying to
> produce some queries without such dependancies. If anyone has an idea how
> to achieve something like this (even if very technical or convoluted) it
> would be much appreciated.
>
> Thanks,
> Ryan
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL Help

[Igor Tandetnik]

On 2/9/2015 7:55 AM, R.Smith wrote:

Which of course works fine if you have a predeterminable set of columns
to extract, such as Months in the above case. How can I do this sort of
thing (i'm only interested in similar results, the method is not
important) for an indetermintate set of columns?


You do it in your application's code. SQLite will help you get the data 
you need, then it's up to you to format it for presentation.


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

[sqlite] SQL Help

[R.Smith]

I used to make Pivot-table-like reports in (what I thought was) the 
normal way:


SELECT ProductID, sum(QtyMade) AS TotQty,
   (sum(CASE  Mth  WHEN  1  THEN  QtyMade  ELSE  0  END)) AS 'Jan Qty'
,(sum(CASE  Mth  WHEN  2  THEN  QtyMade  ELSE  0  END)) AS 'Feb Qty'
  ,(sum(CASE  Mth  WHEN  3  THEN  QtyMade  ELSE  0  END)) AS 'Mar Qty'
...
  ,(sum(CASE  Mth  WHEN 11 THEN  QtyMade  ELSE  0  END)) AS 'Nov Qty'
  ,(sum(CASE  Mth  WHEN 12 THEN  QtyMade  ELSE  0  END)) AS 'Dec Qty'
FROM ProductionHist
WHERE Year=2014
GROUP BY ProductID
ORDER BY TotQty DESC;

etc.

Which of course works fine if you have a predeterminable set of columns 
to extract, such as Months in the above case. How can I do this sort of 
thing (i'm only interested in similar results, the method is not 
important) for an indetermintate set of columns?


   Example data:
   ID  |  Product |  Group|  Qty
   +--++---
   1 |  ABC001  |A   |  10
   2 |  ABC001  |A   |  20
  3 |  ABC002  |A   |  10
   4 |  ABC001  |B   |  10
   5 |  ABC002  |B   |  20
   6 |  ABC001  |F   |  60
   ...
 97 |  ABC001  |n   |  20
 98 |  ABC002  |n   |  30

   To produce a result like:

   Product |  Tot Group A  |  Tot Group B  | ... | Tot Group n
   +---+---+-+-
   ABC001  |   30  |  10   | ... | 20
   ABC002  |   10  |  20   | ... | 30


Both Products and Groups are indeterminate.  I toyed with the idea of 
making a temporary table or CTE with DISTINCT to contain only unique 
values for one set (either Procucts or Groups in the above example) and 
then maybe derive another table and finally a "SELECT * " query which 
would simply reproduce all, but of course that won't work - somewhere 
along the line you need a handle on the number of output columns.


I know this is easy in a program or in a spreadsheet, but I am trying to 
produce some queries without such dependancies. If anyone has an idea 
how to achieve something like this (even if very technical or 
convoluted) it would be much appreciated.


Thanks,
Ryan



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

Re: [sqlite] SQL help

[Paul Sanderson]

Thanks all, dates are stored internally as integers

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

Re: [sqlite] SQL help

[Jean-Christophe Deschamps]

At 18:09 22/12/2011, you wrote:


DELETE FROM t WHERE rowid !=
(SELECT t2.rowid FROM t t2 where t2.num = t.num ORDER BY t2.date LIMIT 1);


Are you sure that date will collate correctly, as entries appear to be 
in dd/mm/ format?


Dates in non_ISO format are always a pain.  IMHO if dates are to be 
stored in string format, then one should always store them in 
/MM/DD (with leading zeroes) then eventually display dates in 
whatever format suits users at the application level. 


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

Re: [sqlite] SQL help

[Igor Tandetnik]

On 12/22/2011 11:00 AM, Simon Davies wrote:

DELETE FROM t WHERE rowid NOT IN (
SELECT rowid FROM
 ( SELECT rowid, num, date FROM t ) CROSS JOIN
 ( SELECT num min_num, min( date ) min_date FROM t GROUP BY num ) ON
 num=min_num AND date=min_date
);


Seems a bit complicated. How about

DELETE FROM t WHERE rowid !=
(SELECT t2.rowid FROM t t2 where t2.num = t.num ORDER BY t2.date LIMIT 1);


If there can be duplicate dates on num, then make it:

DELETE FROM t WHERE rowid NOT IN(
SELECT rid FROM
 ( SELECT MIN( rowid ) rid, num, date FROM t GROUP BY num, date ) CROSS JOIN
 ( SELECT num min_num, MIN( date ) min_date FROM t GROUP BY num ) ON
 num=min_num AND date=min_date
);


Or

DELETE FROM t WHERE rowid !=
(SELECT t2.rowid FROM t t2 where t2.num = t.num ORDER BY t2.date, 
t2.rowid LIMIT 1);


--
Igor Tandetnik

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

Re: [sqlite] SQL help

[Simon Davies]

On 22 December 2011 15:08, Paul Sanderson  wrote:
> Hi I have a large table with some duplicate rows that I want to
> delete. Essentially I have two columns, one containing a date and one
> containing a number. The number column can contain duplicates. For any
> row containing duplicate values I want to remove all rows bar the
> oldest.
>
> i.e. if the data is
>
> 1       1/1/2011
> 2       1/1/2011
> 2       2/1/2011
> 3       1/1/2011
> 3       2/1/2011
> 3       3/1/2011
> 5       7/1/2011
>
> I want to be left with
>
> 1       1/1/2011
> 2       1/1/2011
> 3       1/1/2011
> 5       7/1/2011

CREATE TABLE t( num integer, date text );
INSERT INTO "t" VALUES(1,'1/1/2011');
INSERT INTO "t" VALUES(2,'1/1/2011');
INSERT INTO "t" VALUES(2,'2/1/2011');
INSERT INTO "t" VALUES(3,'1/1/2011');
INSERT INTO "t" VALUES(3,'2/1/2011');
INSERT INTO "t" VALUES(3,'3/1/2011');
INSERT INTO "t" VALUES(5,'7/1/2011');

DELETE FROM t WHERE rowid NOT IN (
SELECT rowid FROM
( SELECT rowid, num, date FROM t ) CROSS JOIN
( SELECT num min_num, min( date ) min_date FROM t GROUP BY num ) ON
num=min_num AND date=min_date
);

If there can be duplicate dates on num, then make it:

DELETE FROM t WHERE rowid NOT IN(
SELECT rid FROM
( SELECT MIN( rowid ) rid, num, date FROM t GROUP BY num, date ) CROSS JOIN
( SELECT num min_num, MIN( date ) min_date FROM t GROUP BY num ) ON
num=min_num AND date=min_date
);


> Paul

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

Re: [sqlite] SQL help

[Petite Abeille]

On Dec 22, 2011, at 4:08 PM, Paul Sanderson wrote:

> I have a large table with some duplicate rows that I want to
> delete.


Something along these lines:

delete 
fromfoo
where   rowid not in
(
  selectmax( rowid )
  from  foo
  group by  bar,
baz
)

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

[sqlite] SQL help

[Paul Sanderson]

Hi I have a large table with some duplicate rows that I want to
delete. Essentially I have two columns, one containing a date and one
containing a number. The number column can contain duplicates. For any
row containing duplicate values I want to remove all rows bar the
oldest.

i.e. if the data is

1   1/1/2011
2   1/1/2011
2   2/1/2011
3   1/1/2011
3   2/1/2011
3   3/1/2011
5   7/1/2011

I want to be left with

1   1/1/2011
2   1/1/2011
3   1/1/2011
5   7/1/2011


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

Re: [sqlite] SQL help

[Dennis Cote]

Ken wrote:

 sum(is_complete)  is  only the same as "where is_complete = 1"  when there is 
a check constraint guaranteeing that is complete will either be a 0 or 1.
 
  
Yes, that is true, and you could sum(is_complete != 0) if you want to be 
extra careful. This uses a C like definition of true as anything except 
zero.


I think its reasonable for an application to assume that a database 
field has a suitable value if it's the application that puts those 
values (i.e. 0 or 1 only for a boolean column) into the database.


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQL help

[Dennis Cote]

Clark Christensen wrote:


Yeah, that's much cleaner.  Just once through the tech's module set instead of 
twice, and it satisfies both requirements (at least some modules, and all 
complete).

Thanks again!

  

Clark,

You are welcome. You should probably look at Igor's idea though.

It will eliminate the comparisons in your application code and return 
the allComplete value directly from the query. It's really very slick.


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQL help

[Clark Christensen]

>You could try this:

>  select count(*) as mcount, sum(is_complete) as mcomplete
>  from tech_modules
>  where tech_id = ? and coll_id = ?;

Dennis,

Yeah, that's much cleaner.  Just once through the tech's module set instead of 
twice, and it satisfies both requirements (at least some modules, and all 
complete).

Thanks again!

 -Clark

- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, April 3, 2007 12:27:54 PM
Subject: Re: [sqlite] SQL help

Clark Christensen wrote:
> I have a table, as described below, where I need to find out if the tech_id 
> in question has at least some modules in a particular collection (coll_id), 
> and they're all complete.
>
> At this point, I'm working with variations on:
>
> select
> (select count(*) from tech_modules
>  where tech_id = ? and coll_id = ?) as mcount,
> (select count(*) from tech_modules where tech_id = ? 
>  and coll_id = ? and is_complete = 1) as mcomplete;
>
> Performing the comparisons on mcount and mcomplete in the app ($mcount > 0 && 
> $mcomplete == $mcount) to get a boolean $allComplete.
>
> It performs OK (~50K rows in the table), using the unique index for both 
> subqueries, but the SQL seems crude.  Anybody have a more elegant solution to 
> share?
>
> Thanks!
>
>  -Clark
> 
> CREATE TABLE TECH_MODULES (
> TECH_ID integer,
> MODULE_ID integer,
> COLL_ID integer,
> IS_COMPLETE integer default 0,
> COMPLETION_TIME date,
> COMPLETION_TARGET date,
> DELETED integer
> );
>
> CREATE INDEX tech_modules_by_module_id ON TECH_MODULES (module_id);
>
> CREATE UNIQUE INDEX tech_modules_unique_module_id on TECH_MODULES (tech_id, 
> coll_id, module_id);
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>   
Clark,

You could try this:

  select count(*) as mcount, sum(is_complete) as mcomplete
  from tech_modules
  where tech_id = ? and coll_id = ?;

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQL help

[Ken]

 sum(is_complete)  is  only the same as "where is_complete = 1"  when there is 
a check constraint guaranteeing that is complete will either be a 0 or 1.
 
Dennis Cote <[EMAIL PROTECTED]> wrote: Clark Christensen wrote:
> I have a table, as described below, where I need to find out if the tech_id 
> in question has at least some modules in a particular collection (coll_id), 
> and they're all complete.
>
> At this point, I'm working with variations on:
>
> select
> (select count(*) from tech_modules
>  where tech_id = ? and coll_id = ?) as mcount,
> (select count(*) from tech_modules where tech_id = ? 
>  and coll_id = ? and is_complete = 1) as mcomplete;
>
> Performing the comparisons on mcount and mcomplete in the app ($mcount > 0 && 
> $mcomplete == $mcount) to get a boolean $allComplete.
>
> It performs OK (~50K rows in the table), using the unique index for both 
> subqueries, but the SQL seems crude. Anybody have a more elegant solution to 
> share?
>
> Thanks!
>
>  -Clark
> 
> CREATE TABLE TECH_MODULES (
> TECH_ID integer,
> MODULE_ID integer,
> COLL_ID integer,
> IS_COMPLETE integer default 0,
> COMPLETION_TIME date,
> COMPLETION_TARGET date,
> DELETED integer
> );
>
> CREATE INDEX tech_modules_by_module_id ON TECH_MODULES (module_id);
>
> CREATE UNIQUE INDEX tech_modules_unique_module_id on TECH_MODULES (tech_id, 
> coll_id, module_id);
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>   
Clark,

You could try this:

  select count(*) as mcount, sum(is_complete) as mcomplete
  from tech_modules
  where tech_id = ? and coll_id = ?;

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQL help

[Dennis Cote]

Clark Christensen wrote:

I have a table, as described below, where I need to find out if the tech_id in 
question has at least some modules in a particular collection (coll_id), and 
they're all complete.

At this point, I'm working with variations on:

select
(select count(*) from tech_modules
 where tech_id = ? and coll_id = ?) as mcount,
(select count(*) from tech_modules where tech_id = ? 
 and coll_id = ? and is_complete = 1) as mcomplete;


Performing the comparisons on mcount and mcomplete in the app ($mcount > 0 && 
$mcomplete == $mcount) to get a boolean $allComplete.

It performs OK (~50K rows in the table), using the unique index for both 
subqueries, but the SQL seems crude.  Anybody have a more elegant solution to 
share?

Thanks!

 -Clark

CREATE TABLE TECH_MODULES (

TECH_ID integer,
MODULE_ID integer,
COLL_ID integer,
IS_COMPLETE integer default 0,
COMPLETION_TIME date,
COMPLETION_TARGET date,
DELETED integer
);

CREATE INDEX tech_modules_by_module_id ON TECH_MODULES (module_id);

CREATE UNIQUE INDEX tech_modules_unique_module_id on TECH_MODULES (tech_id, 
coll_id, module_id);




-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Clark,

You could try this:

 select count(*) as mcount, sum(is_complete) as mcomplete
 from tech_modules
 where tech_id = ? and coll_id = ?;

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sql help

[Michael Gross]

SELECT
  userid,
  SUM(CASE WHEN typeid=1 THEN 1 ELSE 0 END)/COUNT(*) AS percent
FROM
  uses
GROUP BY
  userid



Cory Nelson wrote:

Hey guys, I'm hoping to get some help with this:

create table uses(userid integer, typeid integer);
insert into uses values(1, 1);
insert into uses values(1, 1);
insert into uses values(2, 1);
insert into uses values(2, 2);

now i'm trying to build a select that gives the result:
userid | percent
11.0
20.5

Where percent is the percent of typeids for that userid where typeid=1.

I've been trying to do this unsuccessfully for a couple days, so any
help would be greatly appreciated.

[sqlite] sql help

[Cory Nelson]

Hey guys, I'm hoping to get some help with this:

create table uses(userid integer, typeid integer);
insert into uses values(1, 1);
insert into uses values(1, 1);
insert into uses values(2, 1);
insert into uses values(2, 2);

now i'm trying to build a select that gives the result:
userid | percent
11.0
20.5

Where percent is the percent of typeids for that userid where typeid=1.

I've been trying to do this unsuccessfully for a couple days, so any
help would be greatly appreciated.

-- 
Cory Nelson
http://www.int64.org

Re: [sqlite] SQL Help (Joining and Grouping)

[Gilbert Jeiziner]

Robert Simpson wrote:
> SELECT games.id, team1_id, team2_id, a.name AS team1, b.name AS team2, result
>   FROM games
> INNER JOIN teams AS a ON team1_id = a.id
> INNER JOIN teams AS b ON team2_id = b.id
> WHERE a.name LIKE 'Suns' AND b.name LIKE 'Bulls'
> 
> That'll find all games where the Suns played the Bulls and you can use that 
> as an example of how you'd query for other things as well.
> 

Robert,

excellent. That's exactly what I need. I didn't know that you
could actually inner join the same table twice. That's quite
powerfull.

Thanke you

Gilbert
-- 
"The best portion of a good man's life is his little, nameless,
unremembered acts of kindness and love." - Wordsworth, William

[sqlite] SQL Help (Joining and Grouping)

[Gilbert Jeiziner]

Hello,

I send this once some days ago, but it didn't appear on the list,
so i hope this time it'll work out.

I know this is not directly related to SQLite, but hopefully
someone has the time to help me with a (probably) basic SQL question:

Consider the following tables:

CREATE TABLE teams (id,name);
CREATE TABLE games (id, date, team1_id, team2_id, result);

team1_id and team2_id refer to the id in the teams table.

1. What query would be best suited to get an output so that the
output would contain the teams names (not only the id) and the
dates and results?

2. What query would be best if I want to search for games that
two specific teams had played. For example, I want all the games
that the teams with the names 'bla' and 'blub' played against
each other. Is there any way to do that?

I tried this with joins, with groupings, but wasn't able to get
the lists i wanted. I can get the name of one team, but not the
name of the other team.

Any help would be appreciated

Gilbert

RE: [sqlite] SQL help

[Tim McDaniel]

Dennis Cote came up with this, which I think will work...

select t.* from t join (select max(a) as a, b from t group by b) as key
where t.a=key.a and t.b=key.b;

Thanks for all the suggestions.
Tim

Re: [sqlite] SQL help

[Fred Bleuzet]

Well, isn't the point of the HAVING clause?

SELECT MAX(A), B FROM T
GROUP BY B
HAVING B=5

Fred

RE: [sqlite] SQL help

[Eddy Macnaghten]

Replying to my own post, sorry :-)

Being pedantic here, when I said the "correct" way of doing what is
required was...


> select * from t
> where (a, b) in (select max(a), b from t group by b);

that would work for ORACLE, but is not ANSI as such.

The ANSI method would be...

select * from t x
where a in (select max(a) from t y where x.b = y.b);

However - that does not work under SQLite either :-(

You are stuck with the "hashing" method described in my answer.

If the tables are exceptionally large, and you would need indexing to
kick in to perform the query you may think of adding an extra
column containing the hashed value and populate it prior to
performing the query

update t set ab = (b * 10) + a;

select * from t where ab in (select (b * 10) + max(a) from t);

That would use an index on ab in a lot of SQLs, I do not know about
SQLite.


On Tue, 2004-10-05 at 01:55, Eddy Macnaghten wrote:
> > SELECT MAX(A) AS A, B, C
> > FROM T
> > GROUP BY B
> > 
> 
> This is an invalid SQL statement (SQLite should generate an error here).
> 
> The correct(tm) way to do this is with subqueries.
> 
> ORACLE SQL (and others), using subqueries, you would use...
> 
> select * from t
> where (a, b) in (select max(a), b from t group by b);
> 
> However - SQLite does not support that (two elements in an "in" clause) :-( 
> 
> Can you hash the two columns to give one as such (or similar)?
> 
> > 
> 
> select * from t
> where ((a * 10) + b) in (select (a * 10) + max(b)
> from t group by b);
> 
> 
> 
> which should give you what you want if you can.
> 
> 
> Eddy
> 
-- 
Edward A. Macnaghten
http://www.edlsystems.com

RE: [sqlite] SQL help

[Eddy Macnaghten]

> SELECT MAX(A) AS A, B, C
> FROM T
> GROUP BY B
> 

This is an invalid SQL statement (SQLite should generate an error here).

The correct(tm) way to do this is with subqueries.

ORACLE SQL (and others), using subqueries, you would use...

select * from t
where (a, b) in (select max(a), b from t group by b);

However - SQLite does not support that (two elements in an "in" clause) :-( 

Can you hash the two columns to give one as such (or similar)?

> 

select * from t
where ((a * 10) + b) in (select (a * 10) + max(b)
from t group by b);



which should give you what you want if you can.


Eddy

RE: [sqlite] SQL help

[Tim McDaniel]

> The statement proposed by Lawrence (copied below) will work 
> if your table has only these two columns, A and B.
> 
> SELECT MAX(A) AS A , B
> FROM T
> GROUP BY B
> 
> From your question I got the imprssion you may have other 
> columns as well.
> 
> For a table t like this;
> 
> a|b|c
> 1|5|6
> 2|5|7
> 3|5|8
> 10|6|7
> 4|6|10
> 3|2|5
> 4|2|6
> 7|6|13
> 
> An extended version of Lawrence's query gives;
> 
> sqlite> select max(a), b,c from t group by b;
> max(a)|b|c
> 4|2|5
> 3|5|6
> 10|6|7
> 
> Which has the wrong values for the column c. It uses the 
> value of c from the first record in each group. There is no 
> record with values 4, 2, 5.
> 
> You can get the correct rows with the following query which 
> is similar to Eric's proposal except that it is combined into 
> a single SQL statement.
> 
> select t.* from t join (select max(a) as a, b from t group by 
> b) as key where t.a=key.a and t.b=key.b;
> 
> Which will give the rows
> 
> t.a|t.b|t.c
> 3|5|8
> 10|6|7
> 4|2|6
> 
> This works, but I suspect there is probably a better way to do it.
> 

Thanks!  I think that will work.  At least it points me in the right
direction.
Tim

RE: [sqlite] SQL help

[Tim McDaniel]

> 
> > Given a table T like this:
> 
> > A B
> > -
> >  1 5
> >  2 5
> >  3 5
> > 
> > I need a query to give me just the row with the largest A 
> value, within
> > a group defined by B.  In this case, it would be the row with A = 3.
> 
> SELECT MAX(A) AS A , B
> FROM T
> GROUP BY B
> 
> 

Thanks, this does give me the max value of A within a group, but any
remaining column values still come from the row with A = 1.  For example

table T:

 A B C
---
 1 5 3
 2 5 2
 3 5 1

SELECT MAX(A) AS A, B, C
FROM T
GROUP BY B

gives:

 A B C
---
 3 5 3

But what I need is:

 A B C
---
 3 5 1

Re: [sqlite] SQL help

[Dennis Cote]

Tim McDaniel wrote:
> Given a table T like this:
>
>  A B
> -
>  1 5
>  2 5
>  3 5
>
> I need a query to give me just the row with the largest A value,
> within a group defined by B.

Tim,

The statement proposed by Lawrence (copied below) will work if your table
has only these two columns, A and B.

SELECT MAX(A) AS A , B
FROM T
GROUP BY B

>From your question I got the imprssion you may have other columns as well.

For a table t like this;

a|b|c
1|5|6
2|5|7
3|5|8
10|6|7
4|6|10
3|2|5
4|2|6
7|6|13

An extended version of Lawrence's query gives;

sqlite> select max(a), b,c from t group by b;
max(a)|b|c
4|2|5
3|5|6
10|6|7

Which has the wrong values for the column c. It uses the value of c from the
first record in each group. There is no record with values 4, 2, 5.

You can get the correct rows with the following query which is similar to
Eric's proposal except that it is combined into a single SQL statement.

select t.* from t join (select max(a) as a, b from t group by b) as key
where t.a=key.a and t.b=key.b;

Which will give the rows

t.a|t.b|t.c
3|5|8
10|6|7
4|2|6

This works, but I suspect there is probably a better way to do it.

I hope this helps.

Re: [sqlite] SQL help

[Eric Scouten]

I'm not sure if you can do it in one query, but you can definitely do it 
in two:

SELECT max(A) FROM T WHERE B = 5;
SELECT * FROM T WHERE A = ? AND B = 5;  -- bind ? to max(a) above
Tim McDaniel wrote:
All,
This should be easy, but this non SQL guru can't figure it out...
Given a table T like this:
A B
-
1 5
2 5
3 5
I need a query to give me just the row with the largest A value, within
a group defined by B.  In this case, it would be the row with A = 3.
Something like:
SELECT * FROM T
GROUP BY B
ORDER BY A;
It doesn't seem to matter if I use ASC or DESC for the ordering, it
always gives me the row with A = 1.
Any ideas?
Thanks,
Tim McDaniel
 

Re: [sqlite] SQL help

[Lawrence Chitty]

- Original Message - 
From: "Tim McDaniel" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 04, 2004 11:23 PM
Subject: [sqlite] SQL help


> Given a table T like this:

> A B
> -
>  1 5
>  2 5
>  3 5
> 
> I need a query to give me just the row with the largest A value, within
> a group defined by B.  In this case, it would be the row with A = 3.

SELECT MAX(A) AS A , B
FROM T
GROUP BY B


Lawrence


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.772 / Virus Database: 519 - Release Date: 01/10/04

[sqlite] SQL help

[Tim McDaniel]

All,

This should be easy, but this non SQL guru can't figure it out...

Given a table T like this:

 A B
-
 1 5
 2 5
 3 5

I need a query to give me just the row with the largest A value, within
a group defined by B.  In this case, it would be the row with A = 3.
Something like:

SELECT * FROM T
GROUP BY B
ORDER BY A;

It doesn't seem to matter if I use ASC or DESC for the ordering, it
always gives me the row with A = 1.

Any ideas?

Thanks,
Tim McDaniel