Newcomer needs help with a SQL Query

Hi, I’m new to this list and looking for some help (my SQL is not brillian
t
to say the least) and information I’ve found on the web is confusing me
further.
It’s related to an SMS Web report, but I think it’s more of a SQL relate
d
question.
I am trying to pull a count of all specified programs in use in a collection
with a count of which machines they are installed on. I’ve had no problem
doing this. Query is as follows …
Select DISTINCT arp.DisplayName0, count(*) as 'Count', @.CollID as Collection
ID
FROM v_R_System sys
JOIN v_GS_ADD_REMOVE_PROGRAMS arp ON sys.ResourceID = arp.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
WHERE (arp.DisplayName0 in ('3D+ 4.1 OEM Ed.')
or arp.DisplayName0 in ('3D+ 5.0 (AutoCAD)')
or arp.DisplayName0 in ('3D+ 5.2 (AutoCAD 2004)')
or arp.DisplayName0 in ('3D+ 5.2 (AutoCAD)')
or arp.DisplayName0 in ('3D+ 5.2 OEM Ed.')
or arp.DisplayName0 in ('3D+ ADT 2004')
or arp.DisplayName0 in ('3D+ ADT 2005')
or arp.DisplayName0 in ('3D+ V6.00 (AutoCAD 2004)')
or arp.DisplayName0 in ('Ward Toolkit')
or arp.DisplayName0 in ('West Point Bridge Designer 2005')
or arp.DisplayName0 in ('Xsteel 7.0','Xsteel 7.1','Xsteel 8.0')
or arp.DisplayName0 in ('XTRACT'))
and fcm.CollectionID=@.CollID
GROUP BY DisplayName0
ORDER BY Displayname0
Problem is that arp.DisplayName0 contains some duplicate entries (they are
not actually duplicates, there is a further column in the view called ProdID
0
and this is where the duplicate entries occur).
What I need to do is count only the first occurrence of each item in
arp.DisplayName0 for each computer and ignore the rest. I thought that SELEC
T
DISTINCT would do this, but it has no effect.
Can anyone help?
Thanks in advance …
________________________________________
________
Rob Stack MBCS CITP IEngPlease post DDL, sample data and expected results. Without that any attempt
at helping you is a guessing game.
Se this:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/|||>Select DISTINCT arp.DisplayName0, count(*) as 'Count', @.CollID as CollectionIDed">
>FROM v_R_System sys
>JOIN v_GS_ADD_REMOVE_PROGRAMS arp ON sys.ResourceID = arp.ResourceID
>JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
>WHERE (arp.DisplayName0 in ('3D+ 4.1 OEM Ed.')
>or arp.DisplayName0 in ('3D+ 5.0 (AutoCAD)')
>or arp.DisplayName0 in ('3D+ 5.2 (AutoCAD 2004)')
>or arp.DisplayName0 in ('3D+ 5.2 (AutoCAD)')
>or arp.DisplayName0 in ('3D+ 5.2 OEM Ed.')
>or arp.DisplayName0 in ('3D+ ADT 2004')
>or arp.DisplayName0 in ('3D+ ADT 2005')
>or arp.DisplayName0 in ('3D+ V6.00 (AutoCAD 2004)')
>or arp.DisplayName0 in ('Ward Toolkit')
>or arp.DisplayName0 in ('West Point Bridge Designer 2005')
>or arp.DisplayName0 in ('Xsteel 7.0','Xsteel 7.1','Xsteel 8.0')
>or arp.DisplayName0 in ('XTRACT'))
>and fcm.CollectionID=@.CollID
>GROUP BY DisplayName0
>ORDER BY Displayname0
Just a small aside, you can simplify that WHERE clause quite a bit by
actually using the IN test in a more conventional manner:
WHERE arp.DisplayName0
IN ('3D+ 4.1 OEM Ed.',
'3D+ 5.0 (AutoCAD)',
'3D+ 5.2 (AutoCAD 2004)',
'3D+ 5.2 (AutoCAD)',
'3D+ 5.2 OEM Ed.',
'3D+ ADT 2004',
'3D+ ADT 2005',,
'3D+ V6.00 (AutoCAD 2004)',
'Ward Toolkit',
'West Point Bridge Designer 2005',
'Xsteel 7.0','Xsteel 7.1','Xsteel 8.0',
'XTRACT')
Roy