Re: Query help - partition effective dates and collapse rows

SQL> select * from boats;

   BOAT_ID SER EFFECTIVE_DT
---------- --- ------------

       200 NEW 01/01/1900
       200 CIN 12/11/2002
       200 INS 01/01/2003
       200 COU 01/06/2007
       200 INS 09/09/2008
       200 COU 03/10/2008
       200 COU 01/11/2008
       200 INS 23/11/2008
       200 INS 31/01/2009
       200 INS 03/02/2009
       200 INS 05/04/2009

11 rows selected

SQL>
SQL> select boat_id, service_type, min(effective_dt)   2 from (
  3 select boat_id, service_type, effective_dt, sum(win) over (partition by boat_id order by effective_dt) win_group   4 from (
  5 select boat_id, service_type, effective_dt,   6 case when service_type = lag(service_type) over (partition by boat_id order by effective_dt) then 0 else 1 end win   7 from boats
  8 )) group by boat_id, service_type, win_group   9 order by 3;

   BOAT_ID SER MIN(EFFECTIVE_DT)

---------- --- -----------------
       200 NEW 01/01/1900
       200 CIN 12/11/2002
       200 INS 01/01/2003
       200 COU 01/06/2007
       200 INS 09/09/2008
       200 COU 03/10/2008
       200 INS 23/11/2008

7 rows selected

On Thu, Feb 4, 2010 at 12:30 PM, Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com> wrote:
> Ok Guys/gals, I need a pointer in the right direction as I cannot seem to
> get this query to return the rows I want.
>

-- 
Alex Fatkulin,
http://afatkulin.blogspot.com
http://www.linkedin.com/in/alexfatkulin
--
http://www.freelists.org/webpage/oracle-l