Results 1 to 3 of 3

Thread: Distinct NAMES

  1. #1
    Join Date
    Dec 2010
    Posts
    11

    Talking Unanswered: Distinct NAMES

    PicsCrazy - Pics hosting
    or
    Image

    I wrote this sql to obtain the above data

    select (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) ADDRESS, DISPLAY_NAME
    from DSIPROC.TRB_VW_SUBS_INFO a
    where (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) in
    (select (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) from DSIPROC.TRB_VW_SUBS_INFO b
    group by (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4), DISPLAY_NAME
    having count(STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4) >1 )
    order by (STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4)

    In the image, i only want distinct DISPLAY_NAMES. How do i modify the code??? PLease help me. Thank you.
    Last edited by varunkumar; 01-05-11 at 15:35.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Would

    select distinct <the rest of your code here>

    do any good?

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The whole thing could be rewritten as

    select STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4 ADDRESS, DISPLAY_NAME
    from DSIPROC.TRB_VW_SUBS_INFO a
    group by STREET_ADDRESS_LINE || ' '|| CITY_NAME || ' '|| ZIP_PLUS4
    having count(*) >1
    order by 1;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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