Get duplicate and related data on same table?

[Techno]

using SQL Server 2008

its difficult to explain but actually easy to see.

I have a table:

DeviceHistory
ID
DevID
Value1 (varchar)
Value2 (varchar)


Value1 contains things like:

serialnumber
manufacturercode

think of them as sections/columns/groups. or even better: a heading title.

value2 contains - its related value.

I want to pull results from that table for each of the headings (value1) where the value2 is duplicated but for different DevIDs

makes sense?

[GaryMazzone]

Try some thing like this maybe (coded right here might need to fix up)

Select * From (
    Select 
       DeviceId ,
       Value1 ,
       Value2 ,
       RowNumber() OVER (Partition BY Value2, Value1 Order By DeviceId) AS MyRowNum
     From tableName
) x
Where x.MyRowNum > 1

[Techno]

great. almost my friend!

so now I get these results:

Identifier ValueText1 ValueText2 MyRowNum
2106064 isamirn 633597819001036177 2
2106064 isamirn 633597819001036177 3
2106064 isamirn 633597819001036177 4
8080430 isamirn 633597819001036177 5
8080430 isamirn 633597819001058692 2
2106064 isamirn 633597819001058916 2
8080430 isamirn 633597819001058916 3
8080430 isamirn 633597819001058916 4
8080430 isamirn 633597819001058916 5
8080430 isamirn 633597819001058916 6

thats cool, but, in this case for example:

2106064 isamirn 633597819001058916 2
8080430 isamirn 633597819001058916 3
8080430 isamirn 633597819001058916 4
8080430 isamirn 633597819001058916 5
8080430 isamirn 633597819001058916 6

I only want to show:

8080430 isamirn 633597819001058916 6
2106064 isamirn 633597819001058916 2

how?


I also noticed:

8080430 isamirn 633597819001058692 2

does not show the other devId ?

[GaryMazzone]

If I understand that last one correctly you only want to show the max rownumber (so only once)?

[GaryMazzone]

What about this:

Select 
   DeviceId ,
   Value1 ,
   Value2 ,
   Count(*)
 From tableName
GROUP BY 
	DeviceID,
	Value1,
	Value2
Having Count(*) > 1

[Techno]

correct, once per device but showing the devices that have that same valuetext2

so there are duplicates in that table but I want to filter it so that I get back records where there are no duplicates but only showing the records where the valuetext2 is related to/used on more than 1 devID

[wild_bill]

--mimic your table
declare @myTable table(id int, devid varchar(10), value1 varchar(50), value2 varchar(5))

insert into @myTable(id,devid,value1,value2) Values 
(2106064, 'isamirn', '633597819001058916', '2'),
(8080430, 'isamirn', '633597819001058916', '3'),
(8080430, 'isamirn', '633597819001058916', '4'),
(8080430, 'isamirn', '633597819001058916', '5'),
(8080430, 'isamirn', '633597819001058916', '6') 

--load temp table with distinct list of ids and value1s
SELECT DISTINCT id, value1
INTO #temp
FROM @myTable

--find duplicates
select *
from #temp
where value1 in (
	SELECT value1 
	from #temp
	group by value1
	having count(*) > 1)

[Techno]

What about this:

Select 
   DeviceId ,
   Value1 ,
   Value2 ,
   Count(*)
 From tableName
GROUP BY 
	DeviceID,
	Value1,
	Value2
Having Count(*) > 1

nope. thats worse. LOL

gives me:

Identifier ValueText1 ValueText2 (No column name)
2106064 isamirn 633597819001036177 3
2106064 isamirn 633597819001058916 2
8080430 isamirn 633597819001058916 4

[GaryMazzone]

What about this:

Select DeviceId, Value1,Value2,Count(*) +1 AS Counts FROM (
	Select * From (
		Select 
			DeviceId ,
			Value1 ,
			Value2 ,
			RowNumber() OVER (Partition BY Value2, Value1 Order By DeviceId) AS MyRowNum
		From tableName
	) x
Where x.MyRowNum > 1
)y
Group BY 
	DeviceId, 
	Value1,
	Value2

[Techno]

thankyou.

unfortunately not quite. still the same problem where '633597819001058692' is only showing 1 deviceID related to it, when there are actually 2.

i used to have decent knowledge of SQL a while ago. but now... my brain is just crampt and cannot remember things as I used to!

[Techno]

just to make it easier, i thought i would attach a script with data

be sure to join Device to DeviceHistory on DeviceID in order to get the DeviceIdentifier (on Device table)

just create a blank SQL Server 2008/5 DB and run the script against it.

[Techno]

this *seems* to work:

SELECT DISTINCT(DeviceId), ValueText1, ValueText2
FROM [DeviceHistory]
WHERE [ValueText2] IN
(
SELECT ValueText2
FROM DeviceHistory
WHERE ValueText1 = 'isamirn' AND ValueText2 <> ''
GROUP BY ValueText2
HAVING COUNT(*) > 1
)
ORDER BY ValueText2




but now to alter it so I am not hardcoding it for isamirn but instead for the unique valuetext1 values instead

[Techno]

hmm. almost there. one of the records is actually duplicated for DeviceID and ValueText2 but I want to eliminate that also