> Such a query either returns an undefined answer or generates an error in many other SQL database engines, but in SQLite it does what you would expect:
It may be a useful functionality, but it is NOT what I would expect such a query to return, to be frank.
Also you don't need a nested query in this specific, you can order by checkinTime and limit the result to one.
> select manifest, versionId, checkinTime from version order by checkinTime desc limit 1
or something like that. This should work in SQlite and Postgresql at the minimum. I think to remember that in Oracle you have to use "where rownum=1" so indeed you have to use a nested query. I don't know about other databases.
I agree, that doesn't make sense to me either. What about select versionId, max(checkinTime), min(checkinTime)? Can as well query SqlGPT. And above all, it's not what the SQL standard says when that's the entire point of using a standard in the first place.
Well, it doesn't error out! In this example, it seems like it picks the result from whatever matches the last column, but not sure if this is determinstic:
sqlite> create table x(c1, c2);
sqlite> insert into x values ("a", 1);
sqlite> insert into x values ("b", 2);
sqlite> insert into x values ("c", 3);
sqlite> select c1, max(c2) from x;
c|3
sqlite> select c1, max(c2), min(c2) from x;
a|3|1
sqlite> select c1, min(c2), max(c2) from x;
c|1|3
(note: since SQLite is dynamically typed, no need to specify column types for simple examples like this).
The interesting thing is if you want more than one record, like you want the latest version number for each document ID. In SQLite you could do: `SELECT documentId, versionId, max(checkInTime) FROM version GROUP BY documentId`. In Postgres you can do `SELECT DISTINCT ON (documentId) documentId, versionId, checkInTime FROM version ORDER BY versionId, checkInTime DESC`.
It only allows that if you’ve set it to do so. The default SQL_MODE variable includes ONLY_FULL_GROUP_BY.
However, in their brilliance, AWS RDS defaults to only NO_ENGINE_SUBSTITUTION for SQL_MODE, thus merrily allowing partial aggregates with non-deterministic results. Wheee!
Prior to 5.7, MySQL always accepted non-aggregated fields.
Version 5.7 introduced ONLY_FULL_GROUP_BY, but since that change broke lots of code that depended on this historical behavior, many people disabled it.
randomly, but after filtering by the criteria in the WHERE part of the query. This can actually be useful sometimes if all non-aggregate fields contain the same value (though I wouldn't actually rely on it, since whether this is allowed depends on how the database is configured, and it makes it easy to introduce errors by changing the query)
That query isn't guaranteed to produce a well-defined result in most SQL engines. (For pretty much the same reason the original doesn't/can't/shouldn't…) In the simple case of two rows with the same `checkinTime`, many engines permit the results to be ordered arbitrarily.
It may be a useful functionality, but it is NOT what I would expect such a query to return, to be frank.
Also you don't need a nested query in this specific, you can order by checkinTime and limit the result to one.
> select manifest, versionId, checkinTime from version order by checkinTime desc limit 1
or something like that. This should work in SQlite and Postgresql at the minimum. I think to remember that in Oracle you have to use "where rownum=1" so indeed you have to use a nested query. I don't know about other databases.