Remove duplicates?

[blighter]

Hi 🙂

I have the below report which pulls the auto login details from the registry of scanned computers, but as we have multiple machines per site it lists duplicate values. An example output is:

Username	Displayname	Value
computer1	Comp1		pass1
computer1	Comp1		pass1
computer2	Comp2		pass2
computer3	Comp3		pass3
computer3	Comp3		pass3

Is it possible to remove the duplicates or just return the first result per username?

Select Top 1000000 tblAssets.Username,
  tblADusers.Displayname,
  tblRegistry.Value
From tblAssets
  Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblRegistry.Valuename = 'DefaultPassword'
Order By tblAssets.AssetUnique

Thanks!

[blighter · Accepted solution]

I have found a way to do this now

For those interested:

Select Top 1000000 tblADusers.Displayname As Store,
  tblADusers.Username As Username,
  tblRegistry.Value As Password,
  tblADusers.Userdomain
From tblAssets
  Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblRegistry.Valuename = 'DefaultPassword'
Group By tblADusers.Displayname,
  tblADusers.Username,
  tblRegistry.Value,
  tblADusers.Userdomain