List:General Discussion« Previous MessageNext Message »
From:hsv Date:September 8 2012 4:07am
Subject:Re: Create a VIEW with nested SQL
View as plain text  
>>>> 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.) 

Thread
Create a VIEW with nested SQLMark Haney6 Sep
  • Re: Create a VIEW with nested SQLhsv6 Sep
    • Re: Create a VIEW with nested SQLMark Haney7 Sep
    • Re: Create a VIEW with nested SQLMark Haney7 Sep
      • Re: Create a VIEW with nested SQLhsv8 Sep
      • RE: Create a VIEW with nested SQLRick James10 Sep
        • RE: Create a VIEW with nested SQLhsv11 Sep
  • RE: Create a VIEW with nested SQLRick James7 Sep