title: How to not pull in duplicate rows when joining tables in Vertica SQL
body: <p>Is it possible to not pull in duplicate rows when creating a table from multiple table joins in Vertica SQL? I am wanting to filter out entire duplicate rows - so rows where all column values in that row are the same as another row.</p>
  
  <p>I tried doing this using Group By after searching online, but I still find a large amount of duplicate rows. FYI I am checking for duplicates by downloading my data, opening with excel, and removing duplicates. My end goal is to not have to download the data because we are using a system where we do not have a method for re-loading the data onto the server system we are using (#ThanksCerner).</p>
  
  <p>I was reading a few methods where you create a temporary table and delete duplicates, but also read that it is not recommended to use DELETE for tables with a large # of records, so I was trying to avoid that.</p>
  
  <p>Thanks in advance!</p>
  
  <p>Update: This is the query I am using. I will work on creating some test data I can share for it</p>
  
  <pre><code>    SELECT DISTINCT c.condition_code, c.condition_primary_display, 
       c.effective_date_id AS condition_effective_date,
       bc.member_id, bc.plan_name, bc.payer_name,
       e.facility, e.service_delivery_location, e.hospital_service_display, 
       e.encounter_type_display, e.encounter_type_primary_display, 
       e.discharge_disposition_display, e.admission_source_display, 
       e.status_display, e.actual_arrival_dt_tm, e.discharge_dt_tm,
       p.full_name AS patient_name, p.postal_cd, p.county_display, 
       p.state_primary_display,
       pd.birth_date, pd.ethnicity_primary_display, pd.gender_primary_display,
       pr.race_display,
       po.name AS provider_name, po.provider_id,
       epr.relationship_type_primary_display
      FROM PH_D_Person p
      LEFT OUTER JOIN PH_D_Person_Demographics pd
      ON p.empi_id = pd.empi_id AND  p.population_id = pd.population_id
      LEFT OUTER JOIN PH_D_Person_Race pr
      ON pd.empi_id = pr.empi_id AND pd.population_id = pr.population_id
      LEFT OUTER JOIN PH_F_Condition c
      ON pr.empi_id = c.empi_id AND pr.population_id = c.population_id
      LEFT OUTER JOIN PH_F_Encounter e
      ON c.encounter_id = e.encounter_id AND c.empi_id = e.empi_id AND 
      c.population_id = e.population_id
      LEFT OUTER JOIN PH_F_Encounter_Benefit_Coverage bc
      ON e.encounter_id = bc.encounter_id AND e.empi_id = bc.empi_id AND 
      e.population_id = bc.population_id
      LEFT OUTER JOIN PH_F_Encounter_Personnel_Reltn epr
      ON bc.encounter_id = epr.encounter_id AND bc.empi_id = epr.empi_id AND 
      bc.population_id = epr.population_id
      LEFT OUTER JOIN PH_D_Provider po
      ON epr.provider_id = po.provider_id
      WHERE c.condition_code IN ('J44.0', 'J44.1', 'J44.9', 'J47.1', 'J47.0', 
      'J47.9', 'J43.2', 'J43.9', 'J41.8', 'J41.1', 'J43.8', 'J43.1', 'J41.0', 
      'J43.0', 'J42')
      AND year(e.actual_arrival_dt_tm) ='2017'
      AND year(e.discharge_dt_tm) ='2017'
      ORDER BY bc.member_id DESC;
  </code></pre>