Re: Newbie MySQL question: how to retrieve latest entry from a history of entries?
From: davout (davZZZYYYXXXout_at_dial.pipex.com)
Date: 08/26/04
- Next message: cutthecrap: "Persistence Model"
- Previous message: Frank Bates: "Re: Newbie MySQL question: how to retrieve latest entry from a history of entries?"
- In reply to: Frank Bates: "Re: Newbie MySQL question: how to retrieve latest entry from a history of entries?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Thu, 26 Aug 2004 07:53:44 +0100
Your query isn't accepted by MySQL R4.x
"Frank Bates" <fbates@sbcglobal.net> wrote in message
news:hWcXc.3286$8G2.39@newssvr31.news.prodigy.com...
> 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: cutthecrap: "Persistence Model"
- Previous message: Frank Bates: "Re: Newbie MySQL question: how to retrieve latest entry from a history of entries?"
- In reply to: Frank Bates: "Re: Newbie MySQL question: how to retrieve latest entry from a history of entries?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
