SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 check duplicates with different description
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

526 Posts

Posted - 11/07/2013 :  08:15:43  Show Profile  Reply with Quote
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 - 11/07/2013 :  08:42:55  Show Profile  Reply with Quote
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

Edited by - GhostX1 on 11/07/2013 08:43:32
Go to Top of Page

magmo
Aged Yak Warrior

526 Posts

Posted - 11/07/2013 :  08:51:06  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 11/07/2013 :  08:51:19  Show Profile  Reply with Quote

SELECT ID, TxtNo, Lang, Descr
FROM
(
SELECT *,COUNT(Descr) OVER (PARTITON BY TxtNo) AS Cnt
FROM Table
)t
WHERE Cnt >1 


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

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 11/07/2013 :  09:40:57  Show Profile  Reply with Quote

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;



Edited by - sigmas on 11/07/2013 09:45:11
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/07/2013 :  11:53:21  Show Profile  Reply with Quote

SELECT *
FROM Table t
WHERE NOT EXISTS (SELECT 1 FROM Table
                  WHERE TxtNo = t.TxtNo
                  AND Lang = t.Lang
                  AND Descr <> t.Descr
                 )



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


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

magmo
Aged Yak Warrior

526 Posts

Posted - 11/07/2013 :  12:19:11  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 11/07/2013 :  13:19:00  Show Profile  Reply with Quote
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

Belarus
172 Posts

Posted - 11/07/2013 :  13:49:28  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000