Richard Hipp <d...@sqlite.org> wrote: > 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. > > *Question*: Is there a (better) standard SQL way to do the same thing?
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; or SELECT id, a, b, ..., mtime FROM tab t1 where not exists (select 1 from tab t2 where t2.id = t1.id and t2.mtime > t1.mtime) ORDER BY mtime DESC; I believe these queries are standard, but are likely to be much slower than the original. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
