Nsryjdtyk

I have a query as below, and want to display a row only if the value is 1 using CASE. Please can you advice how I can do that

SELECT DISTINCT 

    a.AccountID, 

    a.ForeName, 

    a.Surname, 

    a.Gender, 

    CASE 

        WHEN B.Value = '1145' THEN '1' 

        WHEN B.Value = '1007' THEN '2' ELSE '0' 

    END AS Value, 

    b.Address,

     b.Town

FROM 

    Customer a

LEFT OUTER JOIN 

    AdditionalDetails b

ON 

    b.ID = a.AccountID

The result I am getting:

 AccountID  ForeName  Surname  Gender NoName  Address Town

  00012       Eric      Manse   Male    0      Porto   Porto

  00013       Peter     Mark    Male    0      Porto   Porto

  00014       Tom       Jerry   Male    0      Porto   Porto

  00014       Tom       Jerry   Male    1      Porto   Porto

  00015       Sarah     Parker  Female  0      Porto   Porto

  00015       Sarah     Parker  Female  1      Porto   Porto

If there is a 1 in the CASE statement, it should not display the 0 just the row with the value 1

I speculate that you want either MAX() or MIN():

SELECT c.AccountID, c.ForeName, c.Surname, c.Gender, 

       MAX(CASE WHEN ad.Value = '1145' THEN '1'

                WHEN ad.Value = '1007' THEN '2'

                ELSE '0' 

           END),

      ad.Address, ad.Town

FROM Customer c LEFT OUTER JOIN

     AdditionalDetails ad

     ON c.ID = ad.ID

GROUP BY c.AccountID, c.ForeName, c.Surname, c.Gender, ad.Address, ad.Town;

EDIT:

You seem to want prioritization:

SELECT cad.*

FROM (SELECT c.AccountID, c.ForeName, c.Surname, c.Gender, 

             ad.Address, ad.Town,

             ROW_NUMBER() OVER (PARTITION BY c.ACCOUNTID

                                ORDER BY (CASE WHEN ad.Value = '1145' THEN 1

                                               WHEN ad.Value = '1007' THEN 2

                                               ELSE 0'

                                          END) DESC

                               ) as seqnum

      FROM Customer c LEFT OUTER JOIN

           AdditionalDetails ad

           ON c.ID = ad.ID

     ) cad

WHERE seqnum = 1;

You have altered your question. You are not looking for distinct rows, but you want to rank the rows and only display best matches.

Depending on your exact requirements you'd use RANK or ROW_NUMBER with an appropriate ORDER BY and PARTITION BY clause for this.

For instance:

select c.*, ad.address, ad.town

from customer c

left join

(

  select

    address,

    town,

    customer_id,

    rank() over (partition by customer_id

                 order by case value when 1145 then 1 when 1007 then 2 else 0 end desc) as rnk

  from additionaldetails

) ad on ad.customer_id = c.id and d.rnk = 1;

you could try like below

 with cte as  (



        SELECT  a.AccountID, a.ForeName, a.Surname, a.Gender,

        b.Address, b.Town,         

        row_number() over(partition by a.AccountID

   order by 

     (CASE WHEN b.Value = '1145' THEN 1 WHEN b.Value = '1007' THEN 2 ELSE 0 

        END) desc) as val

        FROM Customer a

        LEFT OUTER JOIN AdditionalDetails b

        ON b.ID = a.AccountID    

       ) select * from cte where val=1