CODEWITHSUNDEEP
sql-server
xChaax
xChaax

Reputation: 323

Delete or Select Only row with same id but have values for certain fields

I would like to select/delete data with different rows but with same id.

For Example.

ID  ColumnA
A    Honda
A    NULL
B    Yamaha
B    NULL
C    NULL
C    Merc
D    NULL
E    NULL

Output:

ID  ColumnA
A    Honda
B    Yamaha
C    Merc
D    NULL
E    NULL

First thing, I already google for the solutions, but no answers. Any help is greatly appreciated

Upvotes: 0

Views: 71

Answers (4)

Dean Savović
Dean Savović

Reputation: 739

drop table if exists dbo.Motorcycle;

create table dbo.Motorcycle (
     ID char(1)
     , ColumnA varchar(100)
);

insert into dbo.Motorcycle (ID, ColumnA)
values ('A', 'Honda'), ('A', null)
, ('B', 'Yamaha'), ('B', null)
, ('C', null), ('C', 'Merc')
, ('D', null)
, ('E', null);

select
t.ID, t.ColumnA
from (
select
*
, ROW_NUMBER() over (partition by m.ID order by m.ColumnA desc) as RBr
from dbo.Motorcycle m
) t
where t.RBr = 1

Upvotes: 0

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

try this:

declare @tb table(ID varchar(50), ColumnA varchar(50))
insert into @tb 
select 'A',   'Honda' union all 
select 'A' ,   null union all 
select 'B',    'Yamaha' union all 
select 'B' ,   null union all 
select 'C' ,   null union all 
select 'C',    'Merc' union all 
select 'D',    NULL union all 
select  'E',    NULL 




  select a.id,b.ColumnA from
(select count(1) cnt,ID from @tb  group by ID having count(1)>1 or count(1)=1) as a
 left join 
 (select * from @tb) as b  on  a.ID = b.ID
 where b.columnA is not null and cnt>1 or cnt =1

result:

A   Honda
B   Yamaha
C   Merc
D   NULL
E   NULL

Upvotes: 0

user7715598
user7715598

Reputation: 

;With cte(ID,  ColumnA)
AS
(
SELECT 'A','Honda'  Union all
SELECT 'A',NULL     Union all
SELECT 'B','Yamaha' Union all
SELECT 'B',NULL     Union all
SELECT 'C',NULL     Union all
SELECT 'C','Merc'    Union all
SELECT 'D' ,   NULL   Union all
SELECT 'E',    NULL
)
SELECT ID, ColumnA From 
(
SELECT *,ROW_NUMBER()Over(Partition by ID order by ColumnA DESc)AS Seq from cte
)Dt
WHERE dt.Seq =1

Output:

ID  ColumnA
A    Honda
B    Yamaha
C    Merc
D    NULL
E    NULL

Upvotes: 0

TriV
TriV

Reputation: 5148

You could use Row_number and TOP 1 WITH TIES

DECLARE @SampleData AS TABLE
(
    ID varchar(10),
    ColumnA varchar(20)
)

INSERT INTO @SampleData
VALUES
('A', 'Honda'),
('A', NULL),
('B', 'Yamaha'),
('B', NULL),
('C', NULL),
('C', 'Merc'),
('D', NULL),
('E', NULL)

SELECT  TOP (1) WITH TIES 
       sd.ID, 
       sd.ColumnA
FROM @SampleData sd
ORDER BY Row_number() OVER(PARTITION BY sd.ID ORDER BY sd.ColumnA DESC)

Return 

ID  ColumnA
------------
A   Honda
B   Yamaha
C   Merc
D   NULL
E   NULL

Upvotes: 2

Related Questions