Skip site navigation (1) Skip section navigation (2)

Re:

From: <operationsengineer1(at)yahoo(dot)com>
To: Gan Uesli Starling <alias(at)starling(dot)us>, pgsql-novice(at)postgresql(dot)org
Subject: Re:
Date: 2006-02-02 00:38:41
Message-ID: 20060202003841.42937.qmail@web33304.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: 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 

In response to

  • at 2006-01-31 15:10:28 from Gan Uesli Starling

pgsql-novice by date

Next:From: operationsengineer1Date: 2006-02-02 01:13:03
Subject: Re: Swappng Filds
Previous:From: Gan Uesli StarlingDate: 2006-02-01 23:39:13
Subject: Reverting from subtables to no subtables.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group