[sqlite] SELECT DISTINCT question
Thanks. That's helpful. "Last visited" would explain that query.
But now a new puzzle:
> select col1, col2 from table group by col2
> is identical to select col1, distinct col2 from table
The SELECT railroad diagram does not allow DISTINCT in that position, only
right after SELECT?
Should that be:?
select distinct col1, col2 from table
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf
> Sent: Tuesday, 10 May 2016 12:59 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] SELECT DISTINCT question
>
>
> select col1, aggregateFunction(col2) from table group by col3 order by
col1
>
> returns the result of the aggregate function applied to all "col2" values
in
> the "col3" group. The col1 value is the last visited row in the group
which
> triggered the aggregate, with a special case for MIN and MAX, where the
col1
> value is from the last visited row which contained the min or max value of
> col2.
>
> order by is, of course, applied to the result set after it is generated,
and
> may or may not affect the visitation order.
>
> select col1, col2 from table group by col2
>
> is identical to select col1, distinct col2 from table
>
> in that the table is sorted by col2 and each col2 value is reported only
> once. The col1 value is from the last visited row in each group. Order
by
> may be applied "after the fact" to order the result set. Order by may or
may
> not affect the visitation order.
>
> (Note that "last visited" is often stated as "some random row" because the
> visitation order is an implementation detail of the query planner and may
> change from query to query based on the "shape" of the data and the
> particulars of how the query is solved internally.)
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org
> > [mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of
> > dandl
> > Sent: Monday, 9 May, 2016 18:19
> > To: 'SQLite mailing list'
> > Subject: Re: [sqlite] SELECT DISTINCT question
> >
> > The interesting thing about this query is that you can drop any of
> > DISTINCT, GROUP BY or ORDER BY and get the same result.
> >
> > But my question was not "how can I rewrite my query?". It was: how
> > does Sqlite interpret this SQL, given that it's probably invalid?
> >
> > Andl generates code for both Sqlite and Postgres, and I need to know
> > what that code does.
> >
> > Regards
> > David M Bennett FACS
> >
> > Andl - A New Database Language - andl.org
> >
> >
> > > -Original Message-
> > > From: sqlite-users-bounces at mailinglists.sqlite.org
> > > [mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of
> > > Scott Robison
> > > Sent: Monday, 9 May 2016 4:13 PM
> > > To: SQLite mailing list
> > > Subject: Re: [sqlite] SELECT DISTINCT question
> > >
> > > On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
> > >
> > > > Just to add to the below:
> > > >
> > > > S#| SNAME | STATUS | CITY
> > > > ---
> > > > S1| Smith | 20 | London
> > > > S2| Jones | 10 | Paris
> > > > S3| Blake | 30 | Paris
> > > > S4| Clark | 20 | London
> > > > S5| Adams | 30 | Athens
> > > >
> > > > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC
> > > > ;
> > > >
> > > > CITY
> > > > --
> > > > Paris
> > > > London
> > > > Athens
> > > >
> > > > I don't find it easy to explain this result.
> > > >
> > >
> > > My guess based on the available data is that, since you don't
> > > specify
> > which
> > > "S#" you want associated with each city, it is picking the max of
> > > each (coincidentally). If you want the minimum S# value, this seems to
> work:
> > >
> > > select distinct city from s group by city order by min("S#") asc;
> > >
> > > I'm not sure if that *should* work per "standard" SQL, but it does
> > > with SQLite. I'd have expected something like this to be necessary:
> >
[sqlite] SELECT DISTINCT question
> bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf > Why are you using BOTH distinct and group by on the same column? You only > need one or the other. If you are redundantly redundant I would hope that > the optimizer makes redundant (as in gets rid of, for those that are not > English) the redundancies ... This is generated code. Since Andl does not allow any duplicate rows, every SELECT gets a DISTINCT unless the query provably cannot generate duplicates. You need both GROUP BY and DISTINCT in cases where there is an aggregate function (and some others). Say: SELECT DISTINCT SUM(X) AS Y FROM T GROUP BY Z; There is no way to predict from the query how many rows this will generate. Without DISTINCT it can generate duplicates. My question was really about why Sqlite did not complain on what is actually not a valid query. [Andl is still a work in progress.] Regards David M Bennett FACS Andl - A New Database Language - andl.org
[sqlite] SELECT DISTINCT question
Can you point me to something in the docs? It actually isn't quite the same.
Yes, I have discovered the Sqlite behaviour with aggregate functions. In
Postgres and standard SQL it's an error -- the rule is that every column has to
be in the GROUP BY if not used in the SELECT list or as input to an aggregate
function. Sqlite just punts.
I didn't know about DISTINCT. The standard rule here is a bit different: every
column in the ORDER BY must be in the select list. Since Andl generates SQL it
helps if I know what that SQL will do.
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Hick Gunter
> Sent: Monday, 9 May 2016 4:29 PM
> To: 'SQLite mailing list'
> Subject: Re: [sqlite] SELECT DISTINCT question
>
> This is documented behaviour for SQLite:
>
> SELECT a, MAX(b) table;
>
> Will return (one of) the a value(s) that comes from the same row as the
> MAX(b).
>
> If there are not exactly on of MIN or MAX aggregate functions, SQLite is free
> to pick any row (within a group) to return non-aggregated columns from. Thus:
>
> Select a,SUM(b), c from table group by a;
>
> will return one of the c values from each group of a values.
>
> The same thing applies for DISTINCT.
>
>
> -Urspr?ngliche Nachricht-
> Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] Im Auftrag von Scott Robison
> Gesendet: Montag, 09. Mai 2016 08:13
> An: SQLite mailing list
> Betreff: Re: [sqlite] SELECT DISTINCT question
>
> On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
>
> > Just to add to the below:
> >
> > S#| SNAME | STATUS | CITY
> > ---
> > S1| Smith | 20 | London
> > S2| Jones | 10 | Paris
> > S3| Blake | 30 | Paris
> > S4| Clark | 20 | London
> > S5| Adams | 30 | Athens
> >
> > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
> >
> > CITY
> > --
> > Paris
> > London
> > Athens
> >
> > I don't find it easy to explain this result.
> >
>
> My guess based on the available data is that, since you don't specify which
> "S#" you want associated with each city, it is picking the max of each
> (coincidentally). If you want the minimum S# value, this seems to work:
>
> select distinct city from s group by city order by min("S#") asc;
>
> I'm not sure if that *should* work per "standard" SQL, but it does with
> SQLite. I'd have expected something like this to be necessary:
>
> select city, min("S#") as x from s group by city order by x asc;
>
> And if you only want the city:
>
> select city from (select city, min("S#") as x from s group by city order by x
> asc);
>
> But I'm not a SQL master.
>
> Distinct used with group by seems redundant, but again, I might just not
> understand how they are useful together.
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at 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: hick at 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 at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SELECT DISTINCT question
The interesting thing about this query is that you can drop any of DISTINCT,
GROUP BY or ORDER BY and get the same result.
But my question was not "how can I rewrite my query?". It was: how does
Sqlite interpret this SQL, given that it's probably invalid?
Andl generates code for both Sqlite and Postgres, and I need to know what
that code does.
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Scott Robison
> Sent: Monday, 9 May 2016 4:13 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] SELECT DISTINCT question
>
> On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
>
> > Just to add to the below:
> >
> > S#| SNAME | STATUS | CITY
> > ---
> > S1| Smith | 20 | London
> > S2| Jones | 10 | Paris
> > S3| Blake | 30 | Paris
> > S4| Clark | 20 | London
> > S5| Adams | 30 | Athens
> >
> > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
> >
> > CITY
> > --
> > Paris
> > London
> > Athens
> >
> > I don't find it easy to explain this result.
> >
>
> My guess based on the available data is that, since you don't specify
which
> "S#" you want associated with each city, it is picking the max of each
> (coincidentally). If you want the minimum S# value, this seems to work:
>
> select distinct city from s group by city order by min("S#") asc;
>
> I'm not sure if that *should* work per "standard" SQL, but it does with
> SQLite. I'd have expected something like this to be necessary:
>
> select city, min("S#") as x from s group by city order by x asc;
>
> And if you only want the city:
>
> select city from (select city, min("S#") as x from s group by city order
by x
> asc);
>
> But I'm not a SQL master.
>
> Distinct used with group by seems redundant, but again, I might just not
> understand how they are useful together.
>
> --
> Scott Robison
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SELECT DISTINCT question
select col1, aggregateFunction(col2) from table group by col3 order by col1
returns the result of the aggregate function applied to all "col2" values in
the "col3" group. The col1 value is the last visited row in the group which
triggered the aggregate, with a special case for MIN and MAX, where the col1
value is from the last visited row which contained the min or max value of col2.
order by is, of course, applied to the result set after it is generated, and
may or may not affect the visitation order.
select col1, col2 from table group by col2
is identical to select col1, distinct col2 from table
in that the table is sorted by col2 and each col2 value is reported only once.
The col1 value is from the last visited row in each group. Order by may be
applied "after the fact" to order the result set. Order by may or may not
affect the visitation order.
(Note that "last visited" is often stated as "some random row" because the
visitation order is an implementation detail of the query planner and may
change from query to query based on the "shape" of the data and the particulars
of how the query is solved internally.)
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of dandl
> Sent: Monday, 9 May, 2016 18:19
> To: 'SQLite mailing list'
> Subject: Re: [sqlite] SELECT DISTINCT question
>
> The interesting thing about this query is that you can drop any of
> DISTINCT,
> GROUP BY or ORDER BY and get the same result.
>
> But my question was not "how can I rewrite my query?". It was: how does
> Sqlite interpret this SQL, given that it's probably invalid?
>
> Andl generates code for both Sqlite and Postgres, and I need to know what
> that code does.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of Scott Robison
> > Sent: Monday, 9 May 2016 4:13 PM
> > To: SQLite mailing list
> > Subject: Re: [sqlite] SELECT DISTINCT question
> >
> > On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
> >
> > > Just to add to the below:
> > >
> > > S#| SNAME | STATUS | CITY
> > > ---
> > > S1| Smith | 20 | London
> > > S2| Jones | 10 | Paris
> > > S3| Blake | 30 | Paris
> > > S4| Clark | 20 | London
> > > S5| Adams | 30 | Athens
> > >
> > > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
> > >
> > > CITY
> > > --
> > > Paris
> > > London
> > > Athens
> > >
> > > I don't find it easy to explain this result.
> > >
> >
> > My guess based on the available data is that, since you don't specify
> which
> > "S#" you want associated with each city, it is picking the max of each
> > (coincidentally). If you want the minimum S# value, this seems to work:
> >
> > select distinct city from s group by city order by min("S#") asc;
> >
> > I'm not sure if that *should* work per "standard" SQL, but it does with
> > SQLite. I'd have expected something like this to be necessary:
> >
> > select city, min("S#") as x from s group by city order by x asc;
> >
> > And if you only want the city:
> >
> > select city from (select city, min("S#") as x from s group by city order
> by x
> > asc);
> >
> > But I'm not a SQL master.
> >
> > Distinct used with group by seems redundant, but again, I might just not
> > understand how they are useful together.
> >
> > --
> > Scott Robison
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SELECT DISTINCT question
At this point, backward compatibility. Enough people use it expecting it to work that it would be bad to change the behavior. On Mon, May 9, 2016 at 6:42 PM, dandl wrote: > > bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf > > > Why are you using BOTH distinct and group by on the same column? You > only > > need one or the other. If you are redundantly redundant I would hope > that > > the optimizer makes redundant (as in gets rid of, for those that are not > > English) the redundancies ... > > This is generated code. Since Andl does not allow any duplicate rows, every > SELECT gets a DISTINCT unless the query provably cannot generate > duplicates. > You need both GROUP BY and DISTINCT in cases where there is an aggregate > function (and some others). Say: > > SELECT DISTINCT SUM(X) AS Y FROM T GROUP BY Z; > > There is no way to predict from the query how many rows this will generate. > Without DISTINCT it can generate duplicates. > > My question was really about why Sqlite did not complain on what is > actually > not a valid query. [Andl is still a work in progress.] > > Regards > David M Bennett FACS > > Andl - A New Database Language - andl.org > > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Scott Robison
[sqlite] SELECT DISTINCT question
Hick Gunter gave the documented explanation above.
On Mon, May 9, 2016 at 6:18 PM, dandl wrote:
> The interesting thing about this query is that you can drop any of
> DISTINCT,
> GROUP BY or ORDER BY and get the same result.
>
> But my question was not "how can I rewrite my query?". It was: how does
> Sqlite interpret this SQL, given that it's probably invalid?
>
> Andl generates code for both Sqlite and Postgres, and I need to know what
> that code does.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of Scott Robison
> > Sent: Monday, 9 May 2016 4:13 PM
> > To: SQLite mailing list
> > Subject: Re: [sqlite] SELECT DISTINCT question
> >
> > On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
> >
> > > Just to add to the below:
> > >
> > > S#| SNAME | STATUS | CITY
> > > ---
> > > S1| Smith | 20 | London
> > > S2| Jones | 10 | Paris
> > > S3| Blake | 30 | Paris
> > > S4| Clark | 20 | London
> > > S5| Adams | 30 | Athens
> > >
> > > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
> > >
> > > CITY
> > > --
> > > Paris
> > > London
> > > Athens
> > >
> > > I don't find it easy to explain this result.
> > >
> >
> > My guess based on the available data is that, since you don't specify
> which
> > "S#" you want associated with each city, it is picking the max of each
> > (coincidentally). If you want the minimum S# value, this seems to work:
> >
> > select distinct city from s group by city order by min("S#") asc;
> >
> > I'm not sure if that *should* work per "standard" SQL, but it does with
> > SQLite. I'd have expected something like this to be necessary:
> >
> > select city, min("S#") as x from s group by city order by x asc;
> >
> > And if you only want the city:
> >
> > select city from (select city, min("S#") as x from s group by city order
> by x
> > asc);
> >
> > But I'm not a SQL master.
> >
> > Distinct used with group by seems redundant, but again, I might just not
> > understand how they are useful together.
> >
> > --
> > Scott Robison
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Scott Robison
[sqlite] SELECT DISTINCT question
Just to add to the below:
S#| SNAME | STATUS | CITY
---
S1| Smith | 20 | London
S2| Jones | 10 | Paris
S3| Blake | 30 | Paris
S4| Clark | 20 | London
S5| Adams | 30 | Athens
SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
CITY
--
Paris
London
Athens
I don't find it easy to explain this result.
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of dandl
> Sent: Monday, 9 May 2016 11:28 AM
> To: 'SQLite mailing list'
> Subject: [sqlite] SELECT DISTINCT question
>
> I have the following query:
>
> SELECT DISTINCT "EVALA112"("S#") AS "^" FROM "S" ORDER BY "S#" ASC ;
>
> [This is generated code, not hand-written. The table S is from CJ Date
sample
> data.]
>
> This query appears to work correctly. The function is an aggregation, and
> requires the data to be sorted.
>
> This same query fails in Postgres with: "for SELECT DISTINCT, ORDER BY
> expressions must appear in select list".
>
> In effect the reason is that a query in this form requires two sort
> operations, and a single query can have only one. To get this to work
> correctly, I shall either drop the DISTINCT or turn the second part into a
> subselect. That I understand.
>
> The question is: how does Sqlite interpret this query? Why is it not an
> error? Is it because the query returns a single result, which does not
> require a sort?
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SELECT DISTINCT question
I have the following query:
SELECT DISTINCT "EVALA112"("S#") AS "^" FROM "S" ORDER BY "S#" ASC ;
[This is generated code, not hand-written. The table S is from CJ Date
sample data.]
This query appears to work correctly. The function is an aggregation, and
requires the data to be sorted.
This same query fails in Postgres with: "for SELECT DISTINCT, ORDER BY
expressions must appear in select list".
In effect the reason is that a query in this form requires two sort
operations, and a single query can have only one. To get this to work
correctly, I shall either drop the DISTINCT or turn the second part into a
subselect. That I understand.
The question is: how does Sqlite interpret this query? Why is it not an
error? Is it because the query returns a single result, which does not
require a sort?
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
[sqlite] SELECT DISTINCT question
Why are you using BOTH distinct and group by on the same column? You only need
one or the other. If you are redundantly redundant I would hope that the
optimizer makes redundant (as in gets rid of, for those that are not English)
the redundancies ...
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of dandl
> Sent: Sunday, 8 May, 2016 19:45
> To: 'SQLite mailing list'
> Subject: Re: [sqlite] SELECT DISTINCT question
>
> Just to add to the below:
>
> S#| SNAME | STATUS | CITY
> ---
> S1| Smith | 20 | London
> S2| Jones | 10 | Paris
> S3| Blake | 30 | Paris
> S4| Clark | 20 | London
> S5| Adams | 30 | Athens
>
> SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
>
> CITY
> --
> Paris
> London
> Athens
>
> I don't find it easy to explain this result.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
>
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of dandl
> > Sent: Monday, 9 May 2016 11:28 AM
> > To: 'SQLite mailing list'
> > Subject: [sqlite] SELECT DISTINCT question
> >
> > I have the following query:
> >
> > SELECT DISTINCT "EVALA112"("S#") AS "^" FROM "S" ORDER BY "S#" ASC ;
> >
> > [This is generated code, not hand-written. The table S is from CJ Date
> sample
> > data.]
> >
> > This query appears to work correctly. The function is an aggregation,
> and
> > requires the data to be sorted.
> >
> > This same query fails in Postgres with: "for SELECT DISTINCT, ORDER BY
> > expressions must appear in select list".
> >
> > In effect the reason is that a query in this form requires two sort
> > operations, and a single query can have only one. To get this to work
> > correctly, I shall either drop the DISTINCT or turn the second part into
> a
> > subselect. That I understand.
> >
> > The question is: how does Sqlite interpret this query? Why is it not an
> > error? Is it because the query returns a single result, which does not
> > require a sort?
> >
> > Regards
> > David M Bennett FACS
> >
> > Andl - A New Database Language - andl.org
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SELECT DISTINCT question
This is documented behaviour for SQLite:
SELECT a, MAX(b) table;
Will return (one of) the a value(s) that comes from the same row as the MAX(b).
If there are not exactly on of MIN or MAX aggregate functions, SQLite is free
to pick any row (within a group) to return non-aggregated columns from. Thus:
Select a,SUM(b), c from table group by a;
will return one of the c values from each group of a values.
The same thing applies for DISTINCT.
-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Scott
Robison
Gesendet: Montag, 09. Mai 2016 08:13
An: SQLite mailing list
Betreff: Re: [sqlite] SELECT DISTINCT question
On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
> Just to add to the below:
>
> S#| SNAME | STATUS | CITY
> ---
> S1| Smith | 20 | London
> S2| Jones | 10 | Paris
> S3| Blake | 30 | Paris
> S4| Clark | 20 | London
> S5| Adams | 30 | Athens
>
> SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
>
> CITY
> --
> Paris
> London
> Athens
>
> I don't find it easy to explain this result.
>
My guess based on the available data is that, since you don't specify which
"S#" you want associated with each city, it is picking the max of each
(coincidentally). If you want the minimum S# value, this seems to work:
select distinct city from s group by city order by min("S#") asc;
I'm not sure if that *should* work per "standard" SQL, but it does with SQLite.
I'd have expected something like this to be necessary:
select city, min("S#") as x from s group by city order by x asc;
And if you only want the city:
select city from (select city, min("S#") as x from s group by city order by x
asc);
But I'm not a SQL master.
Distinct used with group by seems redundant, but again, I might just not
understand how they are useful together.
--
Scott Robison
___
sqlite-users mailing list
sqlite-users at 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: hick at 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] SELECT DISTINCT question
On Sun, May 8, 2016 at 7:45 PM, dandl wrote:
> Just to add to the below:
>
> S#| SNAME | STATUS | CITY
> ---
> S1| Smith | 20 | London
> S2| Jones | 10 | Paris
> S3| Blake | 30 | Paris
> S4| Clark | 20 | London
> S5| Adams | 30 | Athens
>
> SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ;
>
> CITY
> --
> Paris
> London
> Athens
>
> I don't find it easy to explain this result.
>
My guess based on the available data is that, since you don't specify which
"S#" you want associated with each city, it is picking the max of each
(coincidentally). If you want the minimum S# value, this seems to work:
select distinct city from s group by city order by min("S#") asc;
I'm not sure if that *should* work per "standard" SQL, but it does with
SQLite. I'd have expected something like this to be necessary:
select city, min("S#") as x from s group by city order by x asc;
And if you only want the city:
select city from (select city, min("S#") as x from s group by city order by
x asc);
But I'm not a SQL master.
Distinct used with group by seems redundant, but again, I might just not
understand how they are useful together.
--
Scott Robison
