Sorry... trying to get a lot of info out without a lot of text...
lkup_info is populated when data is loaded, and data is loaded
daily... so MAX(date) would work fine with sub selects, which
don't exist... though I have an auto increment field as well.
I guess I will just have to keep a secondary table that does
a replace on the did... simple enough, just wondoering if it
could be done otherwise...
I have long wished for a way to bring a certain row from a
group by to the top based on some criterion...
Thanks anyway...
On Thu, Jan 24, 2002 at 04:02:55PM -0500, Erv Young wrote:
> Sounds like you have a table (lkup_info) of load dates (date), in which you
> record fileid (did) and date. Sounds like you do not have a separate table
> of fileids. I'm assuming that the lkup_info table is updated synchronously
> with the loading of each file that the table is recording, though you
> didn't say that.
>
> If this table also had an auto-increment numeric primary key
> (lkup_info_id), and if we had a MySQL version that is still only a gleam in
> Monty's eye, it would be a piece of cake:
>
> SELECT * FROM lkup_info a
> WHERE a.lkup_info_id IN
> (SELECT MAX(lkup_info_id)
> FROM lkup_info b
> GROUP BY did)
>
> In the absence of sub-selects, you will need to do the subsidiary select as
> a preliminary step, INTO a temporary table. Then do the main select.
>
> The pain of modifying the query to work without an auto-increment primary
> key, and of waiting (and waiting, and waiting) for it to run, will be far
> worse than the pain of adding an auto-increment primary key if one is not
> already there.
>
> Of course, if the table is not being updated synchronously with loading of
> the files, all this is probably a pipe dream, for which I apologize.
>
> --Erv
>
> At 02:06 PM 1/24/2002 -0500, Anthony R. J. Ball wrote:
> > Is it possible to select only the latest entries
> >from a table?
> >
> > For instance I have a table of datasets that get
> >loaded each day. I want to find the latest date that
> >each dataset was loaded.
> >
> > I thought maybe a join against itself might be the
> >answer, but this doesn't seem to work :)
> >
> >SELECT B.*
> >from lkup_info AS A, lkup_info AS B
> >WHERE A.did = B.did AND MAX(A.date) = B.date GROUP BY A.did;
> >
> > But that is basically what I want...
> >
> > Is this possible, or do I need to maintain a
> >table for latest updates?
>
>
> ---------------------------------------------------------------------
> 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
>
--
___ __ __ __ _ _ ____ _ _ ____ ____
/ __)( )( ) /__\( \/ )( ___) ( \( )( ___)(_ _)
\__ \ )(__)( /(__)\\ / )__) ) ( )__) )(
(___/(______)(__)(__)\/ (____)()(_)\_)(____) (__)
"Tolerance is the virtue of the man without convictions" - Chesterton
---------------------------------------------------------------------
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
