CODEWITHSUNDEEP
sqlsql-server
Brink
Brink

Reputation: 67

Query to return SINGLE DISTINCT row

I have the query below working, the thing is I need to only list each unique "VolumeSerialNumber0" once. There's no shortage of questions and approaches to this problem on SO but they suggest using subqueries and group by clause, but when I try to do that I get an error "columnname is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I feel like it has to be close I'm just not getting the magical syntax perfectly correct.

SELECT        
   dbo.v_R_System.Netbios_Name0, 
   dbo.v_GS_LOGICAL_DISK.TimeStamp, 
   dbo.v_GS_LOGICAL_DISK.Description0, 
   dbo.v_GS_LOGICAL_DISK.DeviceID0, 
   dbo.v_GS_LOGICAL_DISK.DriveType0,
   dbo.v_GS_LOGICAL_DISK.Name0, 
   dbo.v_GS_LOGICAL_DISK.SystemName0, 
   dbo.v_GS_LOGICAL_DISK.VolumeName0, 
   dbo.v_GS_LOGICAL_DISK.VolumeSerialNumber0, 
   dbo.v_GS_PARTITION.Size0, 
   dbo.v_GS_LOGICAL_DISK.FileSystem0
FROM            
   dbo.v_R_System 

   INNER JOIN dbo.v_GS_LOGICAL_DISK 
   ON dbo.v_R_System.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID 

   INNER JOIN dbo.v_GS_PARTITION 
   ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_GS_PARTITION.ResourceID

Upvotes: 0

Views: 198

Answers (1)

Chains
Chains

Reputation: 13167

SELECT        
   MAX(S.Netbios_Name0), 
   MAX(L.TimeStamp), 
   MAX(L.Description0), 
   MAX(L.DeviceID0), 
   MAX(L.DriveType0),
   MAX(L.Name0), 
   MAX(L.SystemName0), 
   MAX(L.VolumeName0), 
   L.VolumeSerialNumber0, 
   MAX(P.Size0), 
   MAX(L.FileSystem0)
FROM            
   dbo.v_R_System S

   INNER JOIN dbo.v_GS_LOGICAL_DISK L
   ON S.ResourceID = L.ResourceID 

   INNER JOIN dbo.v_GS_PARTITION P
   ON L.ResourceID = P.ResourceID
GROUP BY
   L.VolumeSerialNumber0

Upvotes: 4

Related Questions