Is there a way to eliminate duplicate data without using 'distinct' or 'unique' clause?

Question

I cannot use Distinct or Unique clause due to Organisation restrictions. Is there a way to modify the code so that no duplicate values are shown? My code is listed below:

`SELECT PER_PERSON_NAMES_F.TITLE,
               PER_PERSON_NAMES_F.FIRST_NAME,
               PER_PERSON_NAMES_F.LAST_NAME,
               PER_PERSON_NAMES_F.FULL_NAME,
               PER_PERSON_NAMES_F.DISPLAY_NAME,
FROM PER_PERSON_NAMES_F,PER_ALL_PEOPLE_F, PER_PERSONS
WHERE PER_PERSON_NAMES_F.PERSON_ID=PER_ALL_PEOPLE_F.PERSON_ID
AND PER_PERSON_NAMES_F.PERSON_ID=PER_PERSONS.PERSON_ID
AND PER_ALL_PEOPLE_F.PERSON_ID=PER_PERSONS.PERSON_ID
AND name_type='GLOBAL'
ORDER BY PER_PERSON_NAMES_F.FULL_NAME.`

Answer 1

What does

due to Organisation restrictions

mean?

---

One option is to put all columns returned by select statement into group by clause:

  SELECT per_person_names_f.title,
         per_person_names_f.first_name,
         per_person_names_f.last_name,
         per_person_names_f.full_name,
         per_person_names_f.display_name
    FROM per_person_names_f, per_all_people_f, per_persons
   WHERE     per_person_names_f.person_id = per_all_people_f.person_id
         AND per_person_names_f.person_id = per_persons.person_id
         AND per_all_people_f.person_id = per_persons.person_id
         AND name_type = 'GLOBAL'
GROUP BY per_person_names_f.title,
         per_person_names_f.first_name,
         per_person_names_f.last_name,
         per_person_names_f.full_name,
         per_person_names_f.display_name
ORDER BY per_person_names_f.full_name

Answer 2

Use GROUP BY:

SELECT n.TITLE,
       n.FIRST_NAME,
       n.LAST_NAME,
       n.FULL_NAME,
       n.DISPLAY_NAME
FROM   PER_PERSON_NAMES_F n
       INNER JOIN PER_ALL_PEOPLE_F ap
       ON n.PERSON_ID=ap.PERSON_ID
       INNER JOIN PER_PERSONS p
       ON  n.PERSON_ID=p.PERSON_ID
       AND ap.PERSON_ID=p.PERSON_ID
WHERE  name_type='GLOBAL'
GROUP BY
       n.TITLE,
       n.FIRST_NAME,
       n.LAST_NAME,
       n.FULL_NAME,
       n.DISPLAY_NAME
ORDER BY n.FULL_NAME

Or, since PERSON_ID appears to be the primary key for a person:

SELECT MAX(n.TITLE) as title,
       MAX(n.FIRST_NAME) as first_name,
       MAX(n.LAST_NAME) as last_name,
       MAX(n.FULL_NAME) as full_name,
       MAX(n.DISPLAY_NAME) as display_name
FROM   PER_PERSON_NAMES_F n
       INNER JOIN PER_ALL_PEOPLE_F ap
       ON n.PERSON_ID=ap.PERSON_ID
       INNER JOIN PER_PERSONS p
       ON  n.PERSON_ID=p.PERSON_ID
       AND ap.PERSON_ID=p.PERSON_ID
WHERE  name_type='GLOBAL'
GROUP BY
       n.PERSON_ID
ORDER BY n.FULL_NAME

Or use the ROW_NUMBER analytic function:

SELECT n.TITLE,
       n.FIRST_NAME,
       n.LAST_NAME,
       n.FULL_NAME,
       n.DISPLAY_NAME
FROM   (
  SELECT n.TITLE,
         n.FIRST_NAME,
         n.LAST_NAME,
         n.FULL_NAME,
         n.DISPLAY_NAME,
         ROW_NUMBER() OVER (PARTITION BY n.person_id) AS rn
  FROM   PER_PERSON_NAMES_F n
         INNER JOIN PER_ALL_PEOPLE_F ap
         ON n.PERSON_ID=ap.PERSON_ID
         INNER JOIN PER_PERSONS p
         ON  n.PERSON_ID=p.PERSON_ID
         AND ap.PERSON_ID=p.PERSON_ID
  WHERE  name_type='GLOBAL'
) n
WHERE    n.rn = 1
ORDER BY n.FULL_NAME

Alternatively, since the duplicates are probably coming from a one-to-many join then don't JOIN the tables use EXISTS instead:

SELECT n.TITLE,
       n.FIRST_NAME,
       n.LAST_NAME,
       n.FULL_NAME,
       n.DISPLAY_NAME
FROM   PER_PERSON_NAMES_F n
WHERE  EXISTS(
         SELECT 1
         FROM   PER_ALL_PEOPLE_F ap
                INNER JOIN PER_PERSONS p
                ON  ap.PERSON_ID=p.PERSON_ID
         WHERE  n.PERSON_ID=ap.PERSON_ID
         AND    p.name_type='GLOBAL'
       )
ORDER BY n.FULL_NAME

Answer 3

SELECT PER_PERSON_NAMES_F.TITLE,
   PER_PERSON_NAMES_F.FIRST_NAME,
   PER_PERSON_NAMES_F.LAST_NAME,
   PER_PERSON_NAMES_F.FULL_NAME,
   PER_PERSON_NAMES_F.DISPLAY_NAME
  FROM PER_PERSON_NAMES_F,PER_ALL_PEOPLE_F, PER_PERSONS
  WHERE PER_PERSON_NAMES_F.PERSON_ID=PER_ALL_PEOPLE_F.PERSON_ID
AND PER_PERSON_NAMES_F.PERSON_ID=PER_PERSONS.PERSON_ID
AND PER_ALL_PEOPLE_F.PERSON_ID=PER_PERSONS.PERSON_ID
AND name_type='GLOBAL'
GROUP BY PER_PERSON_NAMES_F.TITLE,
     PER_PERSON_NAMES_F.FIRST_NAME,
     PER_PERSON_NAMES_F.LAST_NAME,
     PER_PERSON_NAMES_F.FULL_NAME,
     PER_PERSON_NAMES_F.DISPLAY_NAME
ORDER BY PER_PERSON_NAMES_F.FULL_NAME;

Used Group by clause in the above query

Answer 4

Another alternative is to use MINUS to force distinctness.

SELECT 
SELECT PER_PERSON_NAMES_F.TITLE,
               PER_PERSON_NAMES_F.FIRST_NAME,
               PER_PERSON_NAMES_F.LAST_NAME,
               PER_PERSON_NAMES_F.FULL_NAME,
               PER_PERSON_NAMES_F.DISPLAY_NAME,
FROM PER_PERSON_NAMES_F,PER_ALL_PEOPLE_F, PER_PERSONS
WHERE PER_PERSON_NAMES_F.PERSON_ID=PER_ALL_PEOPLE_F.PERSON_ID
AND PER_PERSON_NAMES_F.PERSON_ID=PER_PERSONS.PERSON_ID
AND PER_ALL_PEOPLE_F.PERSON_ID=PER_PERSONS.PERSON_ID
AND name_type='GLOBAL'
UNION SELECT NULL,NULL,NULL,NULL,NULL FROM DUAL WHERE 1 = 0
ORDER BY PER_PERSON_NAMES_F.FULL_NAME

This solution is stupid, which is perhaps an advantage here. It tells future developers that you knowingly did something obviously stupid, and it will make them wonder why you did it. Because you can't add a comment to your code that says -- I was forced to do this because my organization is run by morons.