in some case, we have to use some programming technique to resolve that
problem, ex: we create a new Connection or new Statement (with one
Connection) . we can have many statement.
rs2 = stmt2.executeQuery("select unique filename, max(received_time) from
file_updates group by orig_file_path");
while (rs2.next()) {
rs1 = stmt1.executeQuery("select filename, received_time, handle from
file_updates where filename="+rs2.getString(1)+" and
received_time="+rs2.getTime(2));
...
}
----- Original Message -----
From: "Shankar Unni" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, November 10, 2001 10:08 AM
Subject: Re: Beginner SQL Question
> 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
>
---------------------------------------------------------------------
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
