Re: [sqlite] SQL help

2019-12-31 Thread 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

Re: [sqlite] SQL help

2019-12-31 Thread 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

Re: [sqlite] SQL help

2019-12-30 Thread 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é

Re: [sqlite] SQL help

2019-12-29 Thread x
g 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 with

Re: [sqlite] SQL help

2019-12-28 Thread 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

Re: [sqlite] SQL help

2019-12-28 Thread 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

Re: [sqlite] SQL help

2019-12-28 Thread 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

Re: [sqlite] SQL help

2019-12-28 Thread 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

Re: [sqlite] SQL help

2019-12-28 Thread 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

2019-12-28 Thread 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

[sqlite] SQL help

2019-12-28 Thread 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

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

2017-03-09 Thread 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 -

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

2017-03-09 Thread 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

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

2017-03-09 Thread 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

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

2017-03-08 Thread 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,

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

2017-03-08 Thread 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,

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

2017-03-08 Thread 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. > >

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

2017-03-08 Thread 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

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

2017-03-08 Thread 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

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

2017-03-08 Thread 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

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

2017-03-08 Thread 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

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

2017-03-08 Thread Hick Gunter
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

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

2017-03-08 Thread 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

[sqlite] SQL help: by-range aggregation

2017-03-08 Thread 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

Re: [sqlite] SQL help on preparing a report

2016-07-31 Thread 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

[sqlite] SQL help on preparing a report

2016-07-31 Thread 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

[sqlite] SQL Help: Finding case insensitive matches

2016-05-13 Thread 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

2016-05-13 Thread 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.

[sqlite] SQL Help: Finding case insensitive matches

2016-05-13 Thread 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

[sqlite] SQL Help: Finding case insensitive matches

2016-05-13 Thread 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

[sqlite] SQL Help: Finding case insensitive matches

2016-05-13 Thread 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

[sqlite] SQL Help: Finding case insensitive matches

2016-05-13 Thread 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

2016-05-13 Thread 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,

Re: [sqlite] SQL Help

2015-02-09 Thread 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

Re: [sqlite] SQL Help

2015-02-09 Thread 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

Re: [sqlite] SQL Help

2015-02-09 Thread 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?

[sqlite] SQL Help

2015-02-09 Thread 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

Re: [sqlite] SQL help

2011-12-23 Thread 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

2011-12-22 Thread 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

Re: [sqlite] SQL help

2011-12-22 Thread 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

Re: [sqlite] SQL help

2011-12-22 Thread 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

Re: [sqlite] SQL help

2011-12-22 Thread 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,

[sqlite] SQL help

2011-12-22 Thread 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

Re: [sqlite] SQL help

2007-04-03 Thread 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

Re: [sqlite] SQL help

2007-04-03 Thread 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

Re: [sqlite] SQL help

2007-04-03 Thread Clark Christensen
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, whe

Re: [sqlite] SQL help

2007-04-03 Thread 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

Re: [sqlite] SQL help

2007-04-03 Thread 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

Re: [sqlite] sql help

2005-09-05 Thread 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);

[sqlite] sql help

2005-09-05 Thread 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

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

2005-02-25 Thread 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

[sqlite] SQL Help (Joining and Grouping)

2005-02-25 Thread 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

RE: [sqlite] SQL help

2004-10-04 Thread 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

2004-10-04 Thread 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

2004-10-04 Thread 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

RE: [sqlite] SQL help

2004-10-04 Thread 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

RE: [sqlite] SQL help

2004-10-04 Thread 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

RE: [sqlite] SQL help

2004-10-04 Thread 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 >

Re: [sqlite] SQL help

2004-10-04 Thread 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

Re: [sqlite] SQL help

2004-10-04 Thread 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

Re: [sqlite] SQL help

2004-10-04 Thread 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 >

[sqlite] SQL help

2004-10-04 Thread 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