Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    15

    Unanswered: Trouuble selecting distinct field.

    Hi,

    I am trying to select distinct record with the following query:


    SELECT --+ PARALLEL(p,4) PARALLEL(ad,4)
    distinct
    p.advertiser AS adv,
    p.sku AS sku,
    p.NAME AS item_name,
    max(p.ad) AS catalog,
    p.advertisercategory AS category,
    ad.name as catalog_name
    FROM product@pc_source p, d_link ad
    WHERE p.ad = ad.id
    AND p.advertiser = 260551
    AND p.sku = 'ALP0110'
    group by
    p.advertiser ,
    p.sku ,
    p.NAME ,
    p.advertisercategory ,
    ad.name;

    However, result returns 2 rows.


    ADV SKU ITEM_NAME CATALOG CATEGORYCATALOG_NAME
    260551 ALP0110 Masters 5.11 Trekking Poles - 1 Pair 10284665Trekking Poles Camp/Hike

    260551 ALP0110 Masters 5.11 Trekking Poles - 1 Pair 10279061Trekking Poles_Summer Accessories_Camp/Hike New Product Catalog


    Does anybody know how can I select only one row?

    thanks,

    Katya

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    which 1 of the two is the correct answer & why is it the correct answer?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It seems that you'll have to further restrict the return data set with another WHERE condition.

    Besides (not that it is wrong, but ...), there's no need to use the DISTINCT keyword when having the GROUP BY clause.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Do you care which of the 2 rows is returned? If you don't then do the following

    SELECT --+ PARALLEL(p,4) PARALLEL(ad,4)
    p.advertiser AS adv,
    p.sku AS sku,
    p.NAME AS item_name,
    max(p.ad) AS catalog,
    p.advertisercategory AS category,
    ad.name as catalog_name
    FROM product@pc_source p, d_link ad
    WHERE p.ad = ad.id
    AND p.advertiser = 260551
    AND p.sku = 'ALP0110'
    and rownum < 2
    group by
    p.advertiser ,
    p.sku ,
    p.NAME ,
    p.advertisercategory ,
    ad.name;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    for starters the rows are distinct. both are unique so naturally you will get
    both rows from your query.

    it looks like you need to identify a primary key.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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