Case / MAX how to eliminate duplicates?

Question

Hello,

I ran the following statement:

select prod.ProductName0 as 'Software Name', prod.ProductVersion0 as 'Version' , sys.Name0 as 'Server Name', sys.Operating_System_Name_and0 as 'Operating system'

from v_GS_INSTALLED_SOFTWARE as prod
inner join v_R_System as sys on sys.ResourceId = prod.ResourceID
join v_FullCollectionMembership col on col.ResourceID = sys.ResourceID
where
( prod.ProductName0 like 'Java % Update%'
or
prod.ProductName0 like 'Java(TM)%'
or
prod.ProductName0 like 'Java SE%'
)
and
prod.ProductName0 != 'Java Auto Updater'
and col.CollectionID = 'UCP00020'
Group by Prod.ProductName0,sys.Name0,sys.Operating_System_Name_and0, prod.ProductVersion0
Order By Sys.Name0, ProductName0

but in the results I would like to exclude the "duplicates" or kind of "duplicates" like:

Software Name Version Server Name Operating system
Java 8 Update 351 8.0.3510.10 SGIMAGE Microsoft Windows NT Server 10.0
Java 8 Update 351 (64-bit) 8.0.3510.10 SGIMAGE Microsoft Windows NT Server 10.0

Any idea?

Thanks,
Dom

Answer 1

Hi @Duchemin, Dominique

Since I still haven't heard from you, I can't directly help you change the code. If you want me to help you change the code, you need to separate the columns in the example and tell you which ones are "duplicates" in your example.

You use group by in your code, according to Prod.ProductName0,sys. Name0,sys. Operating_System_Name_and0, prod. ProductVersion0 is grouped in four columns, which makes only four columns with exactly the same content grouped, which is not what you want.

For example: There are differences in the Version column.

You can adjust your code like this.

select prod.ProductName0 as 'Software Name',max(prod.ProductVersion0) as 'Version', 
       sys.Name0 as 'Server Name',sys.Operating_System_Name_and0 as 'Operating system'
from ...
Group by Prod.ProductName0,sys.Name0,sys.Operating_System_Name_and0
...

You can use max or min according to your needs. After the group by, delete the column where you use max or min.

Best regards, Percy Tang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hello,

I tried:

> select prod.ProductName0 as 'Software Name',max(prod.ProductVersion0) as 'Version' , sys.Name0 as 'Server Name', sys.Operating_System_Name_and0 as 'Operating system' from v_GS_INSTALLED_SOFTWARE as prod inner join v_R_System as sys on sys.ResourceId = prod.ResourceID join v_FullCollectionMembership col on col.ResourceID = sys.ResourceID where ( prod.ProductName0 like 'Java % Update%' or
prod.ProductName0 like 'Java(TM)%' or
prod.ProductName0 like 'Java SE%' ) and prod.ProductName0 != 'Java Auto Updater' and col.CollectionID = 'UCP00020' and sys.Name0 = 'SGIMAGE' Group by Prod.ProductName0,sys.Name0,sys.Operating_System_Name_and0 Order By Sys.Name0, ProductName0

but I am still getting 2 items...

Software Name Version Server Name Operating system Java 8 Update 351 8.0.3510.10 SGIMAGE Microsoft Windows NT Server 10.0 Java 8 Update 351 (64-bit) 8.0.3510.10 SGIMAGE Microsoft Windows NT Server 10.0

Should I rearrange some way for the column "Version" or "Software Name"? I saw you pass the (64-bit) in front of the Version number but it is part of the Software Name column ...

Thanks, Dom