Get duplicate and related data on same table?
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?
Re: Get duplicate and related data on same table?
Try some thing like this maybe (coded right here might need to fix up)
Code:
Select * From (
Select
DeviceId ,
Value1 ,
Value2 ,
RowNumber() OVER (Partition BY Value2, Value1 Order By DeviceId) AS MyRowNum
From tableName
) x
Where x.MyRowNum > 1
Re: Get duplicate and related data on same table?
great. almost my friend!
so now I get these results:
Quote:
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:
Quote:
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:
Quote:
8080430 isamirn 633597819001058916 6
2106064 isamirn 633597819001058916 2
how?
I also noticed:
Quote:
8080430 isamirn 633597819001058692 2
does not show the other devId ?
Re: Get duplicate and related data on same table?
If I understand that last one correctly you only want to show the max rownumber (so only once)?
Re: Get duplicate and related data on same table?
What about this:
Code:
Select
DeviceId ,
Value1 ,
Value2 ,
Count(*)
From tableName
GROUP BY
DeviceID,
Value1,
Value2
Having Count(*) > 1
Re: Get duplicate and related data on same table?
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
Re: Get duplicate and related data on same table?
Code:
--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)
Re: Get duplicate and related data on same table?
Quote:
Originally Posted by
GaryMazzone
What about this:
Code:
Select
DeviceId ,
Value1 ,
Value2 ,
Count(*)
From tableName
GROUP BY
DeviceID,
Value1,
Value2
Having Count(*) > 1
nope. thats worse. LOL :)
gives me:
Quote:
Identifier ValueText1 ValueText2 (No column name)
2106064 isamirn 633597819001036177 3
2106064 isamirn 633597819001058916 2
8080430 isamirn 633597819001058916 4
Re: Get duplicate and related data on same table?
What about this:
Code:
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
Re: Get duplicate and related data on same table?
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!
1 Attachment(s)
Re: Get duplicate and related data on same table?
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.
Re: Get duplicate and related data on same table?
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
Re: Get duplicate and related data on same table?
hmm. almost there. one of the records is actually duplicated for DeviceID and ValueText2 but I want to eliminate that also