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?

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

---

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 ?

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

What about this:

Code:

---

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

---

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

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)

---

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

---

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

---

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!

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.

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

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