Re: Help with query

On Jan 23, 4:10 pm, "AZNewsh" <g..._at_dana.ucc.nau.edu> wrote:

> ID-------Year--------Code------Date-------Action
> 1 2007 1234 1/1/07 A
> 1 2007 1234 1/2/07 B
> 1 2007 1234 1/3/07 C
> 1 2007 4321 1/6/07 A
> 1 2007 4321 1/7/07 C
> 1 2007 4321 1/8/07 B

> I want to identify where ACTION C occurs before Action B where the code
> is the same, in the example above only line 5 would satisfy that
> criteria since ID is the same, year is 2007 code was the same and the
> action C occured on a date before action B

SELECT id
  FROM table c
 WHERE year = '2007'
   AND action = 'C'
   AND exists

       (
         SELECT *
           FROM table b
          WHERE b.id = c.id
            AND b.code = c.code
            AND b.action = 'B'
            AND b.date > c.date
       )

--
Phil R Lawrence