CODEWITHSUNDEEP
sqloracle-database
Musa
Musa

Reputation: 45

How to limit return results

My query returns duplicate rows and I want to return only one row.

select  papf.Full_Name,
          papf.Employee_number,
          papf.DatE_OF_BIRTH,
          peef.last_update_date
        /*,
        petf1.ELEMENT_NAME as MedicalSchemeName*/
from per_all_people_f papf
    ,per_all_assignments_f paaf
    ,PAY_ELEMENT_ENTRIES_F peef
    ,pay_element_types_f petf
    --,fnd_user fnu    
    where (papf.Employee_number) not in
(select 
        papf1.Employee_number

from per_all_people_f papfinner
    ,per_all_assignments_f paafinner
    ,PAY_ELEMENT_ENTRIES_F peefinner
    ,pay_element_types_f petfinner

where   paafinner.person_id = papfinner.person_id
and     peefinner.assignment_id = paafinner.assignment_id
and     peefinner.element_type_id = petfinner.element_type_id

and     upper(petf1.ELEMENT_NAME) like '%Condition%'

and     SYSDATE BETWEEN  papfinner.EFFECTIVE_START_DATE AND    papfinner.EFFECTIVE_END_DATE
and     SYSDATE BETWEEN  paafinner.EFFECTIVE_START_DATE AND    paafinner.EFFECTIVE_END_DATE
and     SYSDATE BETWEEN  peefinner.EFFECTIVE_START_DATE AND    peefinner.EFFECTIVE_END_DATE
and     SYSDATE BETWEEN  petfinner.EFFECTIVE_START_DATE AND    petfinner.EFFECTIVE_END_DATE
)
and     SYSDATE BETWEEN  papf.EFFECTIVE_START_DATE AND    papf.EFFECTIVE_END_DATE
and     SYSDATE BETWEEN  paaf.EFFECTIVE_START_DATE AND    paaf.EFFECTIVE_END_DATE
and     SYSDATE BETWEEN  peef.EFFECTIVE_START_DATE AND    peef.EFFECTIVE_END_DATE
and     SYSDATE BETWEEN  petf.EFFECTIVE_START_DATE AND    petf.EFFECTIVE_END_DATE
and     upper(petf.ELEMENT_NAME) not like '%Condition%'
and     rownum <= 10000
order by peef.last_update_date;
 ....

This query works properly and returns the correct results but the results are duplicated. I only need one unique row

Upvotes: 0

Views: 68

Answers (1)

Ross Bush
Ross Bush

Reputation: 15175

One of the tables you are joining one is returning doubles or more. I bet it is "peef". Perhaps you can only show the MAX(last_update_date) and roll the rest up against that. So just GROUP BY.

select  papf.Full_Name,
        papf.Employee_number,
        papf.DatE_OF_BIRTH,
        last_update_date = MAX(peef.last_update_date)
...

GROUP BY
    papf.Full_Name,
    papf.Employee_number,
    papf.DatE_OF_BIRTH

Upvotes: 1

Related Questions