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
from file_updates
where (filename, received_time) in
(select unique filename, max(received_time)
from file_updates
group by orig_file_path);
This is elegant and reasonably efficient (as long as there's an index on
(filename, received_time)).
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
efficiency).
--
Shankar.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
