Newbie MySQL question: how to retrieve latest entry from a history of entries?

From: davout (davZZZYYYXXXout_at_dial.pipex.com)
Date: 08/25/04 

Date: Wed, 25 Aug 2004 10:50:50 +0100

I'm using MySQL 4.0.16

How do I join two tables - one a master table, the second a detail table
holding multiple status entries for each master table entry - so that a
query can return the master table fields plus the latest status fields (the
status entry with the most recent date)?

table : station
Fields: StationID INTEGER, StationName VARCHAR

e.g.
        1, London
        2 ,Brighton

table: stationstatus
Fields: StationID, StatusDate, Metric1,Metric2,Metric3

e.g.

    1, 23/8/04, 1, 1, 1
    1, 24/8/04, 2, 2, 2
    1, 25/8/04, 3, 3, 3
    2, 23/8/04, 4, 4, 4
    2, 24/8/04, 5, 5, 5
    2, 25/8/04, 6, 6, 6

What I'm looking for is a query that returns

    StationID,StationName,StatusDate,Metric1,Metric2,Metric3

e.g.

    1, London, 3, 3, 3,
    2, Brighton, 6, 6, 6

Any ideas?

Relevant Pages

  • RE: Table as Criteria
    ... pick one table to be used as the master. ... Run an update query for the master table to update its MonthID entry to ...
    (microsoft.public.access.queries)
  • Re: A5000 and Econet
    ... I've got an econet running with 2 BBC Model Bs and 2 Master 128s. ... and running *STATION repeatedly on one of ... To try and rule out the!Boot, I set the number of IDE discs to 0, so ...
    (comp.sys.acorn.networking)
  • Re: Communications controller?
    ... the master that polls the other station. ... the supply voltage wire drop could ... the number) connected on the same bus, ...
    (comp.arch.embedded)
  • Re: Sending key board input across local network
    ... The software solutions, which I will look into, ... ESC keys) will do here -- the manager's station ... that the master app can't do for itself. ...
    (comp.lang.clipper)
  • Re: MS geht mir auf die Eier, nach Updates
    ... Klar habe ich keinen Masterbrowser. ... Darf jetzt jede Station zu einem Masterbrowser werden? ... Warscheinlich darf nur einer zum Master werden. ... diese eine Maschine mal nicht läuft? ...
    (microsoft.public.de.german.win2000.networking)
Quantcast