Select Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Select Query

  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Select Query

    I have a table with following set of data

    zipcode code siteid contactid segcd priority
    02043 H8 1022722357 1321362004 L 1
    02043 H8 1022722357 7301144221 M 2
    02043 H8 1022722357 7251214784 N 3
    02061 H8 1022733344 1424607099 A 1
    02061 H8 1022733344 7207855516 B 2
    02061 H8 1022733344 1487264648 C 3

    I want to roll up the contact ids to site ids based on the priority assigned to the segcd.
    i.e Contactid's having same siteid's should be rolled to that segment having least priority.

    Example.
    Site id 1022722357 has 3 different contact ids with 3 different segcd's . So the three contacts
    1321362004,7301144221,7251214784
    should be rolled to the segcd L which is having least priority .

    In other words my output should like this
    Zipcode Code SegCd count(Siteid) count(contactid)
    02043 H8 L 1 3
    02061 H8 A 1 3


    How can I exactly achieve this.

    Any help would be greatly helpful


    Thanks
    SRK

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    you need to get familiar with analytic functions. Here's the tricky bit .. you just have to aggregate this result set.

    Code:
    Select
       zipcode,
       code,
       siteid,
       contactid,
       segcd,
       priority
    From
       (
       Select
          zipcode,
          code,
          siteid,
          contactid,
          segcd,
          priority,
          Min(priority) Over
             Partition By (siteid) min_siteid_priority
       From
          my_table
       )
    Where
       priority = min_siteid_priority
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width