Remove almost duplicate records from table

Question

Starting from this query

SELECT DISTINCT Name,Manufacturer,Model,RevisionID FROM dbo.COMPUTER_TABLE ORDER BY Name,RevisionID

I get to the following table:

(Name,Manufacturer,Model,RevisionID)

UST8923 | Microsoft | Surface 2 | 1
UST8923 | Microsoft | Surface 2 | 2
UST8923 | Microsoft | Surface 3 | 3
UST8923 | Microsoft | Surface 3 | 4
CAD4258 | Lenovo | Thinkpad P1 | 1
CAD4258 | Lenovo | Thinkpad P1 | 2
CAD4258 | Dell | Latitude 5480 | 3

I need to get only the records with the highest RevisionID and discard the rest. So, I need to get this result:

UST8923 | Microsoft | Surface 3 | 4
CAD4258 | Dell | Latitude 5480 | 3

Any ideas?

If you only need those four columns you can group by `Name,Manufacturer,Model` and apply `max(RevisionID)`. Otherwise do `row_number() over (partition by Name,Manufacturer,Model order by RevisionID desc) as rn` in a CTE and filter `where rn = 1` — dnoeth, Jan 08 '19 at 19:08

score 1 · Answer 1 · answered Jan 08 '19 at 19:09

A correlated subquery is a simple and efficient solution:

select ct.*
from dbo.COMPUTER_TABLE ct
where ct.revisionid = (select max(ct2.revisionid)
                       from COMPUTER_TABLE ct2
                       where ct2.name = ct.name
                      );

forpas · Accepted Answer · 2019-01-08T20:31:50.143

1

You need to GROUP BY Name, Manufacturer

SELECT 
  Name,
  Manufacturer, 
  MAX(Model) AS MaxModel,
  MAX(RevisionID) AS MaxRevisionID 
FROM dbo.COMPUTER_TABLE 
GROUP BY Name, Manufacturer
ORDER BY Name, MAX(Model), MAX(RevisionID)

edited Jan 08 '19 at 20:31

answered Jan 08 '19 at 19:10

forpas

160,666
10
38
76

Hi, I edited my initial question, it seems records are not always duplicate. – Gneo Pompeo Jan 08 '19 at 20:27
Try my edited answer – forpas Jan 08 '19 at 20:32
Why do you exclude the `Thinkpad P1` lines? – forpas Jan 08 '19 at 21:00
Because I just want the highest RevisionID row for each Name. – Gneo Pompeo Jan 08 '19 at 21:29
What about the other columns Manufacturer and Model? Did you try Gordon Linoff's answer? – forpas Jan 08 '19 at 21:43

Remove almost duplicate records from table

2 Answers2