How to remove duplicate list items (keep higher version ) using powershell script?

Question

I am working with SP 2010 Sharepoint Lists where the list items is having unique column " Document No" and each items are having "version". Each time the list items are getting updated/New items are created with the same document no and with new version no(incremental) through workflow. I would like to keep one list item for any Document number and the Version no should be latest.

Below is my sample data and how it looks

I am looking for some trick/Suggestion to get this done. Below is the code which will fetch the list items where all the column values are same. But i would like to compare only two columns "Document Number" and " Document version" and delete duplicate values (it doesnt matter if other columns are having different values)

Add-PSSnapin Microsoft.Sharepoint.Powershell
$w = get-spweb "www.contosa.com"            
$l = $w.Lists.TryGetList("Test");            
$items = $l.Items;            
$f = $l.Fields["Check In Comment"];            
$listType = $l.GetType().Name;            
foreach($item in $items)            
{            
    $itemTitle = $item.Title;            
    if($listType -eq "SPDocumentLibrary")            
    {            
        if($itemTitle -eq ""){$itemTitle = $item["Name"];}            
    }              
    if($item.Versions.Count -gt 0){            
        $vtr = $item.Versions.Count;            
        Write-Host "$itemTitle, has $vtr versions" -foregroundcolor Green;            
    }            
}

Answer 1

This code should do the work - please adjust variables to match Your enviroment. And You can switch between READ and DELETE mode by changing variable $selectTRUE_or_deleteFALSE - I suggest You to execute READ first to be sure, that code works as expected and verify items that will be deleted. Only after that, execute in DELETE mode.

# VARIABLES
$columnName1 = "ColumnInternalName_DocumentNumber"
$columnName2 = "ColumnInternalName_VersionNumber"
$siteUrl = "https://www.contoso.com"  
$libraryName = "TargetLibraryTitle"
$selectTRUE_or_deleteFALSE = $true

$web = $null
$web = Get-SPWeb $siteUrl
$web.title

$list = $null
$list = $web.lists[$libraryName]
$list.title

# 1 - get all items and collect "Document Number" values
$documentNumbers = @()
$query = New-Object Microsoft.SharePoint.SPQuery
$query.ViewAttributes = "Scope='Recursive'"
$query.RowLimit = 100
do
{
    $items = $list.GetItems($query)
    foreach($item in $items)
    {
        $documentNumbers += $item[$columnName1]     
    }   

    $query.ListItemCollectionPosition = $items.ListItemCollectionPosition
} 
while ($query.ListItemCollectionPosition -ne $null) 

# 2 - remove duplicates and get only unique values
$documentNumbers = $documentNumbers | select -Unique

# 3 - get all items again, but this time - query by document number and remove items based on lower version number
foreach($docNumb in $documentNumbers)
{
    $query = New-Object Microsoft.SharePoint.SPQuery
    $query.ViewAttributes = "Scope='Recursive'"
    $query.RowLimit = 100
    $query.Query = "<Where><Eq><FieldRef Name='$columnName1' /><Value Type='Text'>" + $docNumb + "</Value></Eq></Where>"
    do
    {
        # get all items with same document number
        $items = $null
        $items = $list.GetItems($query)

        # order items by version number, so highest number will be the first one
        $items = $items | Sort-Object -Property $columnName2 –Descending

        # get IDs
        $allIds = @();
        foreach($item in $items)
        {
            $allIds += $item.ID;
        }

        # $first will store item with highest version number and $idsToDelete will store all other ids
        $firstId, $idsToDelete = $allIds

        #delete all other items
        foreach($id in $idsToDelete)
        {
            $itemToDelete = $list.GetItemById($id)
            if($selectTRUE_or_deleteFALSE -eq $false)
            {
                $message = "-- " + $itemToDelete.Id + " // " + $itemToDelete.Url
                $itemToDelete.Delete();
                Write-Host " Item deleted: "$message
            }
            else
            {
                Write-Host " -- " $itemToDelete.Id " // " $itemToDelete.Url
            }
        }

        $query.ListItemCollectionPosition = $items.ListItemCollectionPosition
    } 
    while ($query.ListItemCollectionPosition -ne $null) 
}

$web.Dispose()

Write-Host "End" -b DarkGreen -f Green
Write-Host " "
Write-Host " "