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'

#4

hi

know long time this post

another way of doing this
using row number

drop create data
use  tempdb 
go 


drop table emp
go 


create table emp
(
Empl_id    int ,
Add_type    varchar(100) ,
Ph_no  varchar(100) 

)
go

insert into emp  select 66 ,'H',' 44-14424'
insert into emp  select 66 ,'F',' 44-1442'
insert into emp  select 66 ,'C',' 67-384-295'
insert into emp  select 66 ,'B',' (8888) 8440'
insert into emp  select 66 ,'H',' 608 6775'
insert into emp  select 57 ,'B',' 12345'
insert into emp  select 57 ,'C',' 78910'
insert into emp  select 58 ,'C',' 9988'
go
SQL using row number
SELECT a.* 
FROM   (SELECT Row_number() 
                 OVER( 
                   partition BY empl_id 
                   ORDER BY ph_no) AS rn, 
               empl_id, 
               CASE 
                 WHEN add_type = 'B' THEN ph_no 
               END                         AS ph_no 
        FROM   emp) a 
WHERE  a.rn = 1
Results

image


#5

hi

a third way of doing this

using case when and max

drop create data
USE tempdb 

go 

DROP TABLE emp 

go 

CREATE TABLE emp 
  ( 
     empl_id  INT, 
     add_type VARCHAR(100), 
     ph_no    VARCHAR(100) 
  ) 

go 

INSERT INTO emp 
SELECT 66, 
       'H', 
       ' 44-14424' 

INSERT INTO emp 
SELECT 66, 
       'F', 
       ' 44-1442' 

INSERT INTO emp 
SELECT 66, 
       'C', 
       ' 67-384-295' 

INSERT INTO emp 
SELECT 66, 
       'B', 
       ' (8888) 8440' 

INSERT INTO emp 
SELECT 66, 
       'H', 
       ' 608 6775' 

INSERT INTO emp 
SELECT 57, 
       'B', 
       ' 12345' 

INSERT INTO emp 
SELECT 57, 
       'C', 
       ' 78910' 

INSERT INTO emp 
SELECT 58, 
       'C', 
       ' 9988' 

go
SQL using case when max
SELECT empl_id, 
       Max(CASE 
             WHEN add_type = 'B' THEN ph_no 
             ELSE NULL 
           END) AS maxok 
FROM   emp 
GROUP  BY empl_id
Result

image

hi

fourth way of doing it

little complicated

what this will take care of is
if there is more than 1 add_type B record for employee

drop create data .. more than 1 add_type B record for employee scenario
use  tempdb 
go 


drop table emp
go 


create table emp
(
empid    int ,
Add_type    varchar(100) ,
Ph_no  varchar(100) 
)
go

insert into emp select  66 ,'H',' 44-14424'
insert into emp select  66 ,'F',' 44-1442'
insert into emp select  66 ,'C',' 67-384-295'
insert into emp select  66 ,'B',' (8888) 8440'
insert into emp select  66 ,'B',' (999999'
insert into emp select  66 ,'H',' 608 6775'
insert into emp select  57 ,'B',' 12345'
insert into emp select  57 ,'C',' 78910'
insert into emp select  58 ,'C',' 9988'
go
SQL .. little complicated
 ; WITH cte 
     AS (SELECT empid, 
                Count(CASE 
                        WHEN add_type = 'B' THEN 1 
                      END) AS countb 
         FROM   emp 
         GROUP  BY empid) 
SELECT a.empid, 
           a.ph_no 
    FROM   emp a 
           JOIN cte b 
             ON a.empid = b.empid 
    WHERE  b.countb <> 0 
           AND a.add_type = 'B' 
    UNION ALL 
    SELECT a.empid, 
           NULL 
    FROM   emp a 
           JOIN cte b 
             ON a.empid = b.empid 
    WHERE  b.countb = 0 
Results

image


#6

--create table phonenumbers
--(
--Emp_ID int,
--AD_TYPE char(2),
--Phone_Number VARCHAR(100)
--)

--insert into phonenumbers values(66,'H','44-14424')
--insert into phonenumbers values(66,'F','44-1442')
--insert into phonenumbers values(66,'C','67-384-295')
--insert into phonenumbers values(66,'B','(8888) 8440')
--insert into phonenumbers values(66,'H','608 6775')

--insert into phonenumbers values(57,'B','12345')

--insert into phonenumbers values(57,'C','78910')
--insert into phonenumbers values(58,'C','9988')

select EMP_ID,max(case when AD_TYPE = 'B' THEN Phone_Number else null end) as Phone_Number from phonenumbers
group by EMP_ID
order by EMP_ID DESC