Steve Meyers wrote:
> There are no subselects in MySQL (yet). However, they're usually a bad
> idea anyway, which is why it's never been such a big rush to get them in.
Sometimes (just sometimes), there is no way to do it except with a subselect.
For instance, we have a table where we get record updates of a 'file'
(filename, last-update time, handle to where this version of the file
contents are kept).
The way to get the most recent version of each file is
select filename, received_time, handle
where (filename, received_time) in
(select unique filename, max(received_time)
group by orig_file_path);
This is elegant and reasonably efficient (as long as there's an index on
There is no equivalent join or outer join statement that can express this.
The temporary-table solution is somewhat MySQL-specific. The only
*portable* way to do this across DBs is to also maintain some other
boolean flag ("latest"), keep that correctly updated as new updates come
in, and search on that (which is what we'll be doing in the interests of