OK, now we are both confused. There is only 1 instance of each unit in the unit table. Unit_id id the primary key. There are thousands of instances of each unit in the log table. Each record in this table holds unit_id, temperature and date. If I understand correctly, your suggested queries would do what I need provided I added 'AND l.unit_id = ?' and then repeated the query for each unit. I could do this and it may be better than the present kludge but it would involve generating a list of units from the unit table and then looping through the list making a series of select calls. My question was - surely there is a way to do this in a single sql call. > --- John Larsen <[EMAIL PROTECTED]> wrote: > > Geez, do I have to do all your work for you, :) > > First thing I can think of is, > > Ok this makes no sence you have a units table with > > unit_id as its main > > descriptor, but there can be several cases with > the > > same unit_id but you > > want the one with the max date. Date on the other > > hand is stored in > > another table and references date to unit, so how > in > > the world can you > > specify you want the max date record of > > many unit_id records in the units table when > there > > is no identifying > > mark in said table to say which one matches with > the > > max date.. or am I > > missing something? > > > > > > > > > > simon blenkinsop wrote: > > > > >I don't think these would do what I want, I need > > the > > >record with the max date *for each unit*. Each of > > >these would just select the one unit that had the > > max > > >date. > > > > > >Steve > > > > > >--- John Larsen <[EMAIL PROTECTED]> wrote: > > > > > > > > >>Try just doing a WHERE to select the record with > > the > > >>max date. > > >> > > >>SELECT u.unit_id, u.description, > > >>u.lots_of_other_stuff, l.temp, l.date > > >>FROM units u, log l > > >>WHERE u.unit_id = l.unit_id and l.date=(SELECT > > >>MAX(log.date) FROM log) > > >> ^^^ > > >> you may be able to replace log with l > > >> here > > >> but I'm now sure if subqueries use aliases > > >>from the parent. > > >> > > >>Or you can just use your existing query as the > > top > > >>record is the one > > >>you want to keep and tell it to limit output to > 1 > > >>record. > > >> > > >>SELECT > > >> u.unit_id, > > >> u.description, > > >> u.lots_of_other_stuff, > > >> l.temp, > > >> l.date > > >> FROM units u, log l WHERE (u.unit_id = > l.unit_id) > > >> ORDER by u.unit_id, l.date DESC > > >> LIMIT 1 > > >> > > >> > > >> > > >>The first solution would give you mulitple > outputs > > >>if you had multiple > > >>lines with the same maximum date, and the second > > >>will only give you the > > >>first. > > >> > > >>simon blenkinsop wrote: > > >> > > >> > > >> > > >>>I have a table containing the fields unit_id, > > >>>temperature and date. This has data for around > 20 > > >>>units, with many 1,000's of records for each. > > >>>Further information on each unit is in another > > >>> > > >>> > > >>table > > >> > > >> > > >>>with just 1 entry per unit. > > >>> > > >>>I need to select the most recent record for > each > > >>> > > >>> > > >>unit > > >> > > >> > > >>>together with its associated data. I cannot see > > how > > >>> > > >>> > > >>to > > >> > > >> > > >>>do this in a single select. At present I sort > by > > >>> > > >>> > > >>date > > >> > > >> > > >>>DESC and group by unit_id then my perl script > > >>> > > >>> > > >>discards > > >> > > >> > > >>>all but the 1st record for each unit. > > >>> > > >>>The select is: > > >>> > > >>>SELECT > > >>>u.unit_id, > > >>>u.description, > > >>>u.lots_of_other_stuff, > > >>>l.temp, > > >>>l.date > > >>>FROM units u, log l WHERE (u.unit_id = > l.unit_id) > > >>> ORDER by u.unit_id, l.date DESC > > >>> > > >>> > > >>>This involves a lot of (perl) processing and > > wasted > > >>>mysql retrieval, > > >>>there must be a better way. > > >>> > > >>>Any ideas ? > > >>> > > >>>Thanks > > >>> > > >>>Steve > > >>> > > >>> > > >>>__________________________________ > > >>>Do you Yahoo!? > > >>>Yahoo! SiteBuilder - Free, easy-to-use web site > > >>> > > >>> > > >>design software > > >> > > >> > > >>>http://sitebuilder.yahoo.com > > >>> > > >>> > > >>> > > >>> > > >>> > > >> > > >> > > >> > > > > > > > > >__________________________________ > > >Do you Yahoo!? > > >Yahoo! SiteBuilder - Free, easy-to-use web site > > design software > > >http://sitebuilder.yahoo.com > > > > > > > > > > > > > > > > __________________________________ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site > design software > http://sitebuilder.yahoo.com >
__________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
