>>>> 2012/09/07 09:11 -0400, Mark Haney >>>>
All I need is the most recent record for EACH machine ID, THEN to pull the
machine name from the table that has the name in it.
Somehow I'm missing something incredibly obvious here.
<<<<<<<<
That is not certain.
There is a fairly standard, fairly ugly means of getting all the latest records
by some one field:
SELECT *
FROM (SELECT MAX(date) AS date, mach_id
FROM lights
GROUP BY mach_id) AS ll
JOIN lights USING (mach_id, date)
This works if each pair (mach_id, date) is unique--and if it is, better so to
declare it and make an index of it, or maybe make it the primary key. To this
is the name-table joined:
SELECT *
FROM (SELECT MAX(date) AS date, mach_id
FROM lights
GROUP BY mach_id) AS ll
JOIN lights USING (mach_id, date)
JOIN machine USING (mach_id)
As for performance, maybe the suggested index helps (almost every table is owed
a primary key), or maybe to reverse the three tables (call "ll" a virtual
table).
If you really want to make a view out of it, under MySQL it can be only two
views, not one.
(I extensivly use views, but my tables are not big, and the traffic on them is
very little, about ten transactions each week, all by my hand. One of the views
is painfully slow.)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
