Re: Query help - partition effective dates and collapse rows
- From: Alex Fatkulin <afatkulin@xxxxxxxxx>
- To: ChrisDavid.Taylor@xxxxxxxxxxxxxxx
- Date: Thu, 4 Feb 2010 13:06:25 -0500
What you want to do is pretty straightforward using analytics
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@xxxxxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
