Suppose you have a table like this:
CREATE TABLE tab(ID, A, B, C, ..., MTIME);
The ID entries are not unique. For each ID occurrence, the values for A,
B, C, and so forth might (or might not) be different. We do queries like
this:
SELECT id, a, b, ..., mtime
FROM tab
ORDER BY mtime DESC;
*The problem to solve* is this: We want to restrict the output of the
query above to show only the entry with the smallest MTIME for each
distinct ID.
As of SQLite version 3.7.11
<http://www.sqlite.org/releaselog/3_7_11.html>the problem can be
solved like this:
SELECT id, a, b, ..., min(mtime)
FROM tab
GROUP BY id
ORDER BY min(mtime) DESC;
SQLite, unique among SQL database engines as far as I know, will choose for
A, B, C, ... the values of those columns that correspond to the row that
satisfies the min(mtime) aggregate. And so this actually works. On SQLite
versions prior to 3.7.11, the values of A, B, C, .. would be from any
arbitrary row having the corresponding ID. And on more strictly conforming
SQL implementations (ex: PostgreSQL) the query will not even parse because
the GROUP BY clause does not contain every column that is not within an
aggregate function. But the query does work, and works efficiently and
well, in SQLite and seems very convenient.
*Question*: Is there a (better) standard SQL way to do the same thing?
*Context*: The actual application here is Fossil, and the display of all
changes to a particular file in a repository. Queries like the above are
used to generate, for example, a complete history of the changes to the
date/time function logic in SQLite:
http://www.sqlite.org/src/finfo?name=src/date.c
In this example, the "ID" column corresponds to a particular version (SHA1
hash) of a file. That same version might appear in various branches and so
there are duplicate entries in the graph for the same file, which can be
confusing. (Note, the actual
query<http://www.fossil-scm.org/fossil/artifact/65f708f825e?ln=245-270>to
render the "finfo" page is slightly more complex; "tab" is really a
join
of two tables, and the "a, b, c, .." columns are subqueries. But these
complications do not bare upon the question of this email.) To work around
the confused output, we'd like to only see a single row for each ID,
specifically the row with the smallest value of mtime, which means the
first time that a particular version of a file appears in the repository.
For example:
http://www.sqlite.org/src/finfo?name=src/date.c&fco=1
The recent change to Fossil to add the "fco" query parameter to the "finfo"
page is the first time I have actually needed SQLite's min()/max() query
behavior myself, and I now find it *very* convenient. But, I am slightly
troubled by depending on SQL behavior that is only found in SQLite in not
in other database engines.
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
