FAQ
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.

Here's the data layout:

Boat ID, Service Type, Effective Dt
200 NEW 1/1/1900

200 CIN 11/12/2002

200 INS 1/1/2003
200 COU 6/1/2007
200 INS 9/9/2008
200 COU 10/3/2008

200 COU 11/1/2008

200 INS 11/23/2008

200 INS 1/31/2009
200 INS 2/3/2009
200 INS 4/5/2009

What I need to return is the following rows thus collapsing SERVICE_TYPES that are the same when ordered by EFFECTIVE_DT

200 NEW 1/1/1900 <- new partition
200 CIN 11/12/2002 <-new partition

200 INS 1/1/2003 <-new partition
200 COU 6/1/2007 < - new partition
200 INS 9/9/2008 <- new partition

200 COU 10/3/2008 <- Collapse two COU rows down to the min EFFECTIVE_DT (new partition)
200 INS 11/23/2008 <-- Collapse all INS rows down to the minimum EFFECTIVE_DT (new partition)

The above would be the final result set

Does it make any sense what I'm trying to do?

I think I'm going to have to have a subquery that returns the rows with the Effective Dates I want and then select from that subquery but not positive.

Obviously I'm trying to use analytic functions to come up with this.

Thoughts?

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-354-4799
Email: chris.taylor_at_ingrambarge.com

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

Search Discussions

  • Alex Fatkulin at Feb 4, 2010 at 6:06 pm
    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
    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
  • Stefan Moeding at Feb 4, 2010 at 6:06 pm
    Hi,

    I believe the following should do what you are looking for:

    SELECT ...

    FROM (SELECT boat_id, service_type, effective_date,

    row_number() over (partition by boat_id, service_type
    order by effective_date) as rnum
    FROM ...)

    WHERE rnum = 1

    Regards,
    Stefan

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 4, '10 at 5:30p
activeFeb 4, '10 at 6:06p
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase