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

From: Frank Bates (fbates_at_sbcglobal.net)
Date: 08/26/04

  • Next message: davout: "Re: Newbie MySQL question: how to retrieve latest entry from a history of entries?" 
    Date: Thu, 26 Aug 2004 03:37:49 GMT

    Consider:
    select s.*, ss.*
      from station s
         , stationstatus ss
         , (select StationID, max(StatusDate)
             from stationstatus
             group by StationID) m
      where ss.StationId=s.StationID
        and ss.StationId=m.StationID
        and ss.StatusDate=m.StatusDate
      order by ...

    davout wrote:
    > 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?
    >
    >

  • Next message: davout: "Re: Newbie MySQL question: how to retrieve latest entry from a history of entries?"

    Relevant Pages