Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    12

    Unanswered: DISTINCT QUERY..need help!!!

    I have this query:

    SELECT e.encounter_nr, e.encounter_class_nr, e.official_receipt_nr, p.pid, p.name_last, p.name_first, p.date_birth, p.addr_zip, p.sex,p.blood_group FROM care_encounter AS e LEFT JOIN care_person AS p ON e.pid=p.pid WHERE (p.name_last LIKE 'Reyes%') AND e.is_discharged IN (0) AND e.is_discharged IS NOT NULL AND e.status NOT IN ('deleted','hidden','inactive','void')

    However the output display duplicate values. The unique value there is p.pid

    so I tried ( Select DISTINCT(p.pid), e.encounter_nr, e.encounter_class_nr, e.official_receipt_nr, p.name_last, p.name_first, p.date_birth, p.addr_zip, p.sex,p.blood_group FROM care_encounter ....)

    but result is the same...still returns duplicate values...

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The DISTINCT keyword ensures that there are no duplicate rows meaning that no two rows will have the exact same values in all of the columns.

    What you seem to want is a single row for each p.pid, right? In order to achieve that, you need to group the rows by p.pid using the GROUP BY clause. At that point, the real question arises: which values do you want to return for the different columns and a single p.pid value? Remember, SQL is a set-oriented language and sets are not sorted. So there is no "first" row or so. You'll have to choose the minimum value, maximum or some other value. It is up to you to answer that question.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2011
    Posts
    12
    I have resolve it.Thanx..Here is my query:
    SELECT min(e.encounter_nr)as encounter_nr , min(e.encounter_class_nr), min(e.official_receipt_nr), p.pid, max(p.name_last), min(p.name_first), min(p.date_birth), min(p.addr_zip), min(p.sex), min(p.blood_group) FROM care_encounter AS e LEFT JOIN care_person AS p ON e.pid=p.pid WHERE (p.name_last LIKE 'Reyes%') AND e.is_discharged IN (0) AND e.is_discharged IS NOT NULL AND e.status NOT IN ('deleted','hidden','inactive','void') GROUP BY p.pid

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In the query which adodb21 reached, each column's values were selected from (possibly)different rows.
    That might satisfy adodb21's requirement.

    But, if you want to take all values from a specific single row for each pid,
    Serge Rielau provided some examples of the topic in "SQL on Fire! Part 1" under the title "Retrieving MAXimun row".

    http://www.iiug.org/waiug/present/Fo...rge_Rielau.ppt
    Last edited by tonkuma; 05-21-11 at 21:02. Reason: Correct spelling error.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    lotsa crap in that powerpoint presentation, no offence to you tonkuma

    "groupwise maximum" is a common problem, here are 11 solutions --> Biggest Country of each Continent (groupwise maximum)

    by the way, adodb21, you want an INNER JOIN, not a LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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