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
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
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é
___
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
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@mailing
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
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 c
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 amoun
Thanks Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 onl
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 fro
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
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
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 th
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
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
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
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 whi
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..
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,
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
> > combin
von Simon Slavin
Gesendet: Mittwoch, 08. März 2017 14:53
An: SQLite mailing list
Betreff: Re: [sqlite] SQL help: by-range aggregation
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*
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
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 boun
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 li
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 fi
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!
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
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 ge
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
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|F
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
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_us
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 ge
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 f
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?
Y
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
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
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
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
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 wa
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,
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
To: sqlite-users@sqlite.org
Sent: Tuesday, April 3, 2007 3:07:44 PM
Subject: Re: [sqlite] SQL help
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
&
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
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 eli
(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
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
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 te
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 t
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);
i
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
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
mail-Version: 0.89.5.0
X-Enigmail-Supports: pgp-inline, pgp-mime
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Subject: [sqlite] SQL Help (Joining and Grouping)
X-SmarterMail-Spam: SPF_Pass
X-Rcpt-To: <[EMAIL PROTECTED]>
Hello,
I send this once some days ago,
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,n
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
Well, isn't the point of the HAVING clause?
SELECT MAX(A), B FROM T
GROUP BY B
HAVING B=5
Fred
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
w
> 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 g
> 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
>
>
> > 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
> G
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 th
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
- 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
>
&g
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
65 matches
Mail list logo