Am 05.09.2012 16:57, schrieb Richard Hipp:
On Wed, Sep 5, 2012 at 10:53 AM, Olaf Schmidt <s...@online.de> wrote:
Am 05.09.2012 15:58, schrieb Igor Tandetnik:
Well, you could do something like this:
SELECT id, a, b, ..., mtime FROM tab t1
where mtime = (select min(mtime) from tab t2 where t2.id=t1.id)
ORDER BY mtime DESC;
Ah, nice ... this solves the problem of the potential
"non-uniqueness" of mtime... (was fiddeling along with
something like that too, but so far without a result).
Just for completeness (in case mtime *is* unique):
SELECT id, a, b, ..., mtime FROM tab
WHERE mtime IN (SELECT Min(mtime) FROM tab GROUP BY id)
ORDER BY mtime DESC
In the actual use case that inspired this question (and for which, after
seeing the alternatives, I think I'll stick with SQLite's magical
processing of min())
Sure, I've always seen this special Grouping-behaviour of SQLite
as a feature (when no "SQL-portability" needed to be considered).
the mtime value is a floating point number, and we all
know the hazards of comparing floating point numbers for equality, right?
Hmm, out of interest, considering that in this case - where the
query doesn't contain any Float/String-conversion and no dynamic
calculations (just plain Byte-by-Byte comparison of the stored
SQLite.Values?) - would it really be that risky?
Olaf
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
