Category: SQL Server

28 Aug 2017 20:30

Joined:	22/04/2014
Level:	Bronze
Points:	6

Points:	5

Check duplicate names in SQL server table

Hi all,
i have below table like this.
id name class
1 A null
2 B null
3 c null
4 d null
1 A 9
3 c 9

how to check data with bit value is exist or not. i need below output.

i tried this... but not working
SELECT Id,name,class
(case when isnull(class,123)=123 then 0 else 1 end) as exist
FROM test_Table.

i need below output
id name class exist
2 B null 0
4 d null 0
1 A 9 1
3 c 9 1

regards
Kishore

#769355

04 Sep 2017 18:08

Joined:	13/01/2015
Level:	Silver
Points:	230

Points:	1

Hi Kishore,

Please try below code, it results as per your expectations:

SELECT Id,name,class,ROW_NUMBER() over ( order by id desc) row,
(case when isnull(class,123)=123 then 0 else 1 end) as exist
into #t
FROM test_Table

--select * from #t

delete from #t
where row not in
(select max(row)
from #t
group by id,name)

select Id,name,class,exist from #t
drop table #t

Thanks!
Anjali Bansal

~Give your best and lead the world

#769402

12 Oct 2017 17:07

Joined:	21/07/2014
Level:	Silver
Points:	155

Points:	2

Hi,
You can use group by function in your query. Ok let me tell you,your query should be like:-


Select Id,Name,max(class)class,Max(exist)exist from
(SELECT Id,Name,class,(case when isnull(class,99)=99 then 0 else 1 end) as exist FROM Tbl1)t 
group by Id,Name

Thanks.

#769418

07 Nov 2017 14:05

Joined:	06/04/2010
Level:	Gold
Points:	857

Points:	4

Hi,

Use the below SQL Script to Check duplicate names


DECLARE @duplicate TABLE (
  id int,
  name char(1),
  class int
)

INSERT INTO @duplicate (id, name, class)
  VALUES (1, 'A', NULL)
INSERT INTO @duplicate (id, name, class)
  VALUES (2, 'B', NULL)
INSERT INTO @duplicate (id, name, class)
  VALUES (3, 'c', NULL)
INSERT INTO @duplicate (id, name, class)
  VALUES (4, 'd', NULL)
INSERT INTO @duplicate (id, name, class)
  VALUES (1, 'A', 9)
INSERT INTO @duplicate (id, name, class)
  VALUES (3, 'c', 9)


SELECT
  id,
  name,
  MAX(class) class,
  COUNT(*) exist
FROM @duplicate
GROUP BY id,
         name

Please mark this as Answer, if this helps

Regards
Siva

Check duplicate names in SQL server table

About dotnetspider.com

Quick Links

General

Services