user3262364 user3262364 - 2 months ago 6
SQL Question

how to use distinct only for some items in select query in sql

i have a query like this

select F.F_Stand,
T_Exhibitor.F_Exhibitor_Name,
f.F_Localcontact,
F.F_status
from T_Exhibitor_followup F
left join T_Exhibiton
on T_Exhibitor.F_Exhibitor_Code = F.F_Exhibitor_Code
where F_Exhibition_Code='12511' and
F_Area='DWTC' and
F_Building='SAEED' and
F_catcode='FN'


my out put like this :

Stand no Name number status
Saeed E136 A. Proctor Group Ltd 123 Pending
Saeed E136 A. Proctor Group Ltd 123 Pending
Saeed E136 A. Proctor Group Ltd 123 Pending
Saeed E136 A. Proctor Group Ltd 123 Delivered


I want to get only 1 row out put,if status all Delivered then i want show status 'Deliverd' if any one of status 'Pending' then i want to show status 'Pending'
so i added query like this :in this query i added distinct keyword

select distinct F.F_Stand, T_Exhibitor.F_Exhibitor_Name,f.F_Localcontact,F.F_status
from T_Exhibitor_followup F left join T_Exhibitor on T_Exhibitor.F_Exhibitor_Code=F.F_Exhibitor_Code
where F_Exhibition_Code='12511' and F_Area='DWTC' and F_Building='SAEED' and F_catcode='FN'


but now i am getting out put like this :

Saeed E136 A. Proctor Group Ltd 123 Delivered
Saeed E136 A. Proctor Group Ltd 123 Pending


still not getting

Answer

This is an aggregation query. You can do something like this:

SELECT F.F_Stand,
       e.F_Exhibitor_Name,
       f.F_Localcontact,
       CASE WHEN MIN(F.F_status) = MAX(f.F_status)
            THEN MIN(F.F_status)
            WHEN SUM(CASE WHEN F.F_status = 'Pending' THEN 1 ELSE 0 END) > 0
            THEN 'Pending'
            ELSE '???'
       END
FROM T_Exhibitor_followup F
LEFT JOIN T_Exhibitor e
    ON e.F_Exhibitor_Code = F.F_Exhibitor_Code 
WHERE F_Exhibition_Code = '12511' AND
      F_Area = 'DWTC' AND
      F_Building = 'SAEED' AND
      F_catcode = 'FN'
GROUP BY F.F_Stand,
         e.F_Exhibitor_Name,
         f.F_Localcontact;

The logic for the case is:

  • If all statuses are the same, then use that status.
  • If any status is pending then call it 'Pending'.
  • Otherwise, output '???'.
Comments