Re: - Mailing list pgsql-novice

> 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