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') 

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

---

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;

---

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
                 )

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;