SQLTeam.com | Weblogs | Forums

Select distinct on one column get one row


#1

I have below table A

Empl_id   Add_type    Ph_no

      66          H         44-14424
      66           F         44-1442
      66          C         67-384-295
      66          B         (8888) 8440
      66          H         608 6775
      57          B         12345
      57          C         78910
       58          C         9988

I need to get distinct emp_id and phone_number where if Address =B then ph_number else null as phone_number

my output should be as below where SQL picks Add_type as B and pulls ph_no else shows null

Empl_id      Ph_no
66           (8888) 8440
57           12345
58           NULL

I have tried below query:

select distinct y.emp_id, case when Add_type='B' then i.iphone else null end
ip from (SELECT empl_id,Add_type as phty,ph_no as ph,Row_number()
OVER(PARTITION BY employee_id ORDER BY employee_id) rn
FROM
(SELECT DISTINCT
E.empl_id,E.Add_type,E.ph_no FROM Table Emp E )t )y
left outer join
(SELECT
e1.empl_id,Add_type,E1.ph_no, case when Add_type='B' then ph_no else null
end phone
FROM Table Emp E1
group by E1.employee_id, Address,
E1.phone_number )i
on y.empl_id=i.empl_id
WHERE y.rn = 1 and i.emp_id='66'

The output I get of above query is

emp_id  ph
66         (8888) 8440
66          NULL

I get 2 rows, I need to have one with the ph_no. Where am I doing it wrong?


#2
Select
    addresses.Empl_id,
    addresses.Add_type,
    addresses.Ph_no
From (
   Select DISTINCT
       employees.Empl_id
   From Emp
   ) employees
   Left Join Emp addresses
       On addresses.Empl_id = employees.Empl_id
           And Add_type = 'B'