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
with
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
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
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
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
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
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 -
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
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
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,
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,
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.
> >
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
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
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
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
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
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
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
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
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
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?
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
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,
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
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
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
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);
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
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
> 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
> 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
>
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
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
- 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
47 matches
Mail list logo