?
Solved

select statement required for distinct rows from SMS Database

Posted on 2008-06-18
7
Medium Priority
?
745 Views
Last Modified: 2013-11-21
Hi, We have SMS installed which is sat on a SQL Backend.  We want to report on all the PC's in our organisation with Microsoft Visio installed.  SMS however stores a line for the PC with Visio Version 10 and a line for Version 11 if it has had both versions installed.  I want to create a query that shows me one report for all those NOW on version 11 (regardless of whether they have a line for version 10) and one report for all those STILL on version 10 (ie no line with V11 on).  Ideally I would also like to count these but would like to deal with that separately.  

The current query below returns all the lines of PC's and within that data are duplicate PC's where they have a Version 10 and 11 installed.  These amount to 154.  I know however that there are actually only 145 PC's with Visio on so 9 have been upgraded and are showing two lines.  The code is as follows:-

select distinct SMS_R_System.ResourceId, SMS_R_System.Name, SMS_G_System_SoftwareProduct.ProductName, SMS_G_System_SoftwareProduct.ProductVersion from  SMS_R_System inner join SMS_G_System_SoftwareProduct on SMS_G_System_SoftwareProduct.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareProduct.ProductName = "Microsoft Visio" order by SMS_R_System.ResourceId

Any help would greatly be appreciated!!
0
Comment
Question by:happyappys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 500 total points
ID: 21811334
Try this:

select SMS_R_System.ResourceId,
           SMS_R_System.Name,
           SMS_G_System_SoftwareProduct.ProductName,
           max(SMS_G_System_SoftwareProduct.ProductVersion)
from  SMS_R_System
           inner join SMS_G_System_SoftwareProduct
                on SMS_G_System_SoftwareProduct.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_SoftwareProduct.ProductName = "Microsoft Visio"
group by SMS_R_System.ResourceId,
               SMS_R_System.Name,
               SMS_G_System_SoftwareProduct.ProductName
order by SMS_R_System.ResourceId
0
 

Author Comment

by:happyappys
ID: 21811506
Hi, thanks for this, however when I enter the query in it tells me there's a syntax error?
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 21811544
I can't run this against my database as I don't have your tables.  Does the error give you an indication of where the syntax is incorrect?
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:happyappys
ID: 21812086
Hi again, it seems that I can enter this as a query in the Reporting section of SMS but not in the Query section of SMS.  Unfortunatley our main man has just restricted my access by mistake and I'm waiting for it to reinstate.  I've tried in Enterprise Manager and it works so ignore the syntax error.  If I also wanted to now just show the versions currently on V11 how would this change?  Once again many thanks for your assistance!
0
 

Author Comment

by:happyappys
ID: 21812146
This seems to work with the right results:-

SELECT     TOP (100) PERCENT dbo.v_R_System.ResourceID, dbo.v_R_System.Name0, dbo.vSMS_G_System_SoftwareProduct.ProductName,
                      MAX(dbo.vSMS_G_System_SoftwareProduct.ProductVersion) AS Expr1
FROM         dbo.v_R_System INNER JOIN
                      dbo.vSMS_G_System_SoftwareProduct ON dbo.v_R_System.ResourceID = dbo.vSMS_G_System_SoftwareProduct.ClientId
WHERE     (dbo.vSMS_G_System_SoftwareProduct.ProductName = 'Microsoft Visio')
GROUP BY dbo.v_R_System.ResourceID, dbo.v_R_System.Name0, dbo.vSMS_G_System_SoftwareProduct.ProductName
HAVING      (MAX(dbo.vSMS_G_System_SoftwareProduct.ProductVersion) = '10.0')
ORDER BY dbo.v_R_System.ResourceID

Unless there's anything obvious, if I just change the HAVING criteria to 10.0 for the V10 Query and 11.0 for the V11 query I'm guessing this all looks OK?
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 21812238
Hi happyappys, your query looks okay to me.  
0
 

Author Closing Comment

by:happyappys
ID: 31468247
Great stuff, thanks!
0

Featured Post

Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Issue: One Windows 2008 R2 64bit server on the network unable to connect to a buffalo Device (Linkstation) with firmware version 1.56. There are a total of four servers on the network this being one of them. Troubleshooting Steps: Connect via h…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question