Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 check duplicates with different description

Author  Topic 

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-11-07 : 08:15:43
Hi

I have a table like this....




GO

/****** Object: Table [dbo].[NotesInfoTest] Script Date: 2013-11-07 14:08:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[NotesInfoTest](
[ID] [int] NULL,
[TxtNo] [char](5) NULL,
[Lang] [char](2) NULL,
[Descr] [nvarchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



INSERT INTO NotesInfoTest (ID, TxtNo, Lang, Descr) VALUES (1, '00001', 'SE', 'test')
INSERT INTO NotesInfoTest (ID, TxtNo, Lang, Descr) VALUES (2, '00002', 'GB', 'Book')
INSERT INTO NotesInfoTest (ID, TxtNo, Lang, Descr) VALUES (3, '00001', 'SE', 'test2')







I would like to find all rows that have the same TxtNo and Lang but different "Descr". Can someone show me how to do this?

GhostX1
Starting Member

6 Posts

Posted - 2013-11-07 : 08:42:55
select a.*
from cws.dbo.NotesInfoTest a
inner join (select TxtNo, Lang from cws.dbo.NotesInfoTest
Group by TxtNo, Lang having COUNT(*)>1) b
on a.TxtNo=b.TxtNo and a.Lang=b.Lang
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-11-07 : 08:51:06
quote:
Originally posted by GhostX1

select a.*
from cws.dbo.NotesInfoTest a
inner join (select TxtNo, Lang from cws.dbo.NotesInfoTest
Group by TxtNo, Lang having COUNT(*)>1) b
on a.TxtNo=b.TxtNo and a.Lang=b.Lang



Great, thanks a bunch!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 08:51:19
[code]
SELECT ID, TxtNo, Lang, Descr
FROM
(
SELECT *,COUNT(Descr) OVER (PARTITON BY TxtNo) AS Cnt
FROM Table
)t
WHERE Cnt >1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-07 : 09:40:57
[code]
declare @t TABLE (
[ID] [int] NULL,
[TxtNo] [char](5) NULL,
[Lang] [char](2) NULL,
[Descr] [nvarchar](50) NULL
)

INSERT INTO @t (ID, TxtNo, Lang, Descr) VALUES (1, '00001', 'SE', 'test')
INSERT INTO @t (ID, TxtNo, Lang, Descr) VALUES (2, '00002', 'GB', 'Book')
INSERT INTO @t (ID, TxtNo, Lang, Descr) VALUES (3, '00002', 'GB', 'Book')
INSERT INTO @t (ID, TxtNo, Lang, Descr) VALUES (4, '00001', 'SE', 'test2')


SELECT ID, TxtNo, Lang, Descr
FROM (
SELECT MIN(Descr) OVER(PARTITION BY TxtNo, Lang) mi,
MAX(Descr) OVER(PARTITION BY TxtNo, Lang) mx,
*
FROM @t
)D
WHERE mi < mx;
[/code]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 11:53:21
[code]
SELECT *
FROM Table t
WHERE NOT EXISTS (SELECT 1 FROM Table
WHERE TxtNo = t.TxtNo
AND Lang = t.Lang
AND Descr <> t.Descr
)
[/code]

[code]
SELECT t.*
FROM Table t
OUTER APPLY (SELECT COUNT(*) AS Cnt
FROM Table
WHERE TxtNo = t.TxtNo
AND Lang = t.Lang
AND Descr <> t.Descr
)t1
WHERE COALESCE(t1.Cnt,0) = 0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-11-07 : 12:19:11
quote:
Originally posted by sigmas


declare @t TABLE (
[ID] [int] NULL,
[TxtNo] [char](5) NULL,
[Lang] [char](2) NULL,
[Descr] [nvarchar](50) NULL
)

INSERT INTO @t (ID, TxtNo, Lang, Descr) VALUES (1, '00001', 'SE', 'test')
INSERT INTO @t (ID, TxtNo, Lang, Descr) VALUES (2, '00002', 'GB', 'Book')
INSERT INTO @t (ID, TxtNo, Lang, Descr) VALUES (3, '00002', 'GB', 'Book')
INSERT INTO @t (ID, TxtNo, Lang, Descr) VALUES (4, '00001', 'SE', 'test2')


SELECT ID, TxtNo, Lang, Descr
FROM (
SELECT MIN(Descr) OVER(PARTITION BY TxtNo, Lang) mi,
MAX(Descr) OVER(PARTITION BY TxtNo, Lang) mx,
*
FROM @t
)D
WHERE mi < mx;








This works great and find most all the rows with this criteria. But unfortonatly there is one more criteria that I need to find. The TextNo Can also look like this....

S0001 or H0001, I need to strip of the "S" and "H" so that it only search for the number part of the string. How can I do that?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 13:19:00
quote:
Originally posted by magmo

quote:
Originally posted by sigmas


declare @t TABLE (
[ID] [int] NULL,
[TxtNo] [char](5) NULL,
[Lang] [char](2) NULL,
[Descr] [nvarchar](50) NULL
)

INSERT INTO @t (ID, TxtNo, Lang, Descr) VALUES (1, '00001', 'SE', 'test')
INSERT INTO @t (ID, TxtNo, Lang, Descr) VALUES (2, '00002', 'GB', 'Book')
INSERT INTO @t (ID, TxtNo, Lang, Descr) VALUES (3, '00002', 'GB', 'Book')
INSERT INTO @t (ID, TxtNo, Lang, Descr) VALUES (4, '00001', 'SE', 'test2')


SELECT ID, TxtNo, Lang, Descr
FROM (
SELECT MIN(Descr) OVER(PARTITION BY TxtNo, Lang) mi,
MAX(Descr) OVER(PARTITION BY TxtNo, Lang) mx,
*
FROM @t
)D
WHERE mi < mx;








This works great and find most all the rows with this criteria. But unfortonatly there is one more criteria that I need to find. The TextNo Can also look like this....

S0001 or H0001, I need to strip of the "S" and "H" so that it only search for the number part of the string. How can I do that?



SELECT *
FROM Table t
WHERE NOT EXISTS (SELECT 1 FROM Table
WHERE REPLACE(REPLACE(TxtNo,'S',''),'H','') = REPLACE(REPLACE(t.TxtNo,'S',''),'H','')
AND Lang = t.Lang
AND Descr <> t.Descr
)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-11-07 : 13:49:28
Okay,

SELECT ID, TxtNo, Lang, Descr
FROM (
SELECT MIN(Descr) OVER(PARTITION BY STUFF(TxtNo,1,1,''), Lang) mi,
MAX(Descr) OVER(PARTITION BY STUFF(TxtNo,1,1,''), Lang) mx,
*
FROM @t /*your real table name here*/
)D
WHERE mi < mx;
Go to Top of Page
   

- Advertisement -