Re:

> My problem is this:
>
> I have a table 'history' with columns for
> ''date_time', 'event', 'sensor'.
>
> The 'event' column may contain 'Calibrated',
> 'Damaged', 'Repaired'.
>
> Since it is a history, the same sensor may be listed
> multiple times with a
> date for each event.
>
> I want only the most recent calibration event for
> undamaged sensors.
>
> I am trying to figure out how to winnow out in a
> single query (no
> intermediate
> tables) the most recent event for each sensor which
> has been 'Calibrated' or
> 'Repaired' not 'Damaged' afterwards.
>
> I had thought it would be so simple. But after much
> wrangling I seem to be
> stumped.
>
> --
> Mistera Sturno - Rarest Extinct Bird
>
> <(+)__ Gan Uesli Starling
> ((__/)=- Kalamazoo, MI, USA
> `||`
> ++ http://starling.us
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will
> ignore your desire to
> choose an index scan if your joining column's
> datatypes do not
> match

kalamazoo? kool! i'm no SQL expert, but i've been
helped a lot, so let me give it a shot.

SELECY sensor, event
FROM history
WHERE event = "Calibrated"
OR event = "Repaired"
ORDER BY date_time DESC

should list every sensor record that has Calibrated or
Repaired listed - in date descending order.

we need to fine tune this, though.

how about updating it to...

WHERE event IN (SELECT sensor, event
FROM history
WHERE event = "Calibrated"
ORDER BY date_time DESC
LIMIT 1)
OR event IN (SELECT sensor, event
FROM history
WHERE event = "Repaied"
ORDER BY date_time DESC
LIMIT 1)

this should yield the two results you want. be
careful, i may have botched the syntax a bit (pay
special attention to the IN clause), but you should
get the idea. if you get it working, do post the
answer.

SELECT sensor, event, date-time
FROM history

WHERE history_id IN (SELECT hsitory_id
FROM history
WHERE event = "Calibrated"
ORDER BY date_time DESC
LIMIT 1)
OR history_id IN (SELECT history_id
FROM history
WHERE event = "Repaired"
ORDER BY date_time DESC
LIMIT 1)

ORDER BY date_time DESC

i hope this helps.

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com