Groups in C API

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Groups in C API

Stephan Buchert-2
Thanks for the replies.

Allowing non-aggregate columns in aggregate queries is very useful, as
shown with the min/max functions.

Probably with this feature comes that SQLite even allows all non-aggregate
columns in SELECTs with GROUP BY. Perhaps the documentation should warn
more clearly, that in this case only one arbitrary row in each group is
returned, not all the rows that the WHERE filter lets through.

More useful would perhaps be, to return in this case (only non-aggregate
columns but a GROUP BY)  all rows, just grouped together is indicated by
the GROUP BY. This would have a similar effect as an ORDER BY, but they are
somewhat different if I look at the syntax diagrams. I have no idea how
feasible it would be to get SQLite doing this.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Groups in C API

Kees Nuyt
On Tue, 30 Jan 2018 12:16:32 +0100, Stephan Buchert
<[hidden email]> wrote:

> Thanks for the replies.
>
> Allowing non-aggregate columns in aggregate queries is very useful, as
> shown with the min/max functions.

It is forbidden in most SQL dialects, only supported by SQLite
as a dirty shortcut.

> Probably with this feature comes that SQLite even allows all non-aggregate
> columns in SELECTs with GROUP BY. Perhaps the documentation should warn
> more clearly, that in this case only one arbitrary row in each group is
> returned, not all the rows that the WHERE filter lets through.

IMHO it is pretty clear in https://sqlite.org/lang_select.html 
"3. Generation of the set of result rows"
and its "Side note: Bare columns".

Also, just above "1. Determination of input data" it states:
There are two types of simple SELECT statement - aggregate and
non-aggregate queries. A simple SELECT statement is an aggregate
query if it contains either a GROUP BY clause or one or more
aggregate functions in the result-set. Otherwise, if a simple
SELECT contains no aggregate functions or a GROUP BY clause, it
is a non-aggregate query.


> More useful would perhaps be, to return in this case (only non-aggregate
> columns but a GROUP BY)  all rows, just grouped together is indicated by
> the GROUP BY. This would have a similar effect as an ORDER BY, but they are
> somewhat different if I look at the syntax diagrams. I have no idea how
> feasible it would be to get SQLite doing this.

ORDER BY does the job just fine, there's no need to overload
GROUP BY with that functionality.

--
Regards,
Kees Nuyt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Groups in C API

Hick Gunter
In reply to this post by Stephan Buchert-2
The feature in last paragraph is already the case with plain ORDER BY. All records in "group" order.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Stephan Buchert
Gesendet: Dienstag, 30. Jänner 2018 12:17
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Groups in C API

Thanks for the replies.

Allowing non-aggregate columns in aggregate queries is very useful, as shown with the min/max functions.

Probably with this feature comes that SQLite even allows all non-aggregate columns in SELECTs with GROUP BY. Perhaps the documentation should warn more clearly, that in this case only one arbitrary row in each group is returned, not all the rows that the WHERE filter lets through.

More useful would perhaps be, to return in this case (only non-aggregate columns but a GROUP BY)  all rows, just grouped together is indicated by the GROUP BY. This would have a similar effect as an ORDER BY, but they are somewhat different if I look at the syntax diagrams. I have no idea how feasible it would be to get SQLite doing this.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users