Count Versions
-
I am having a tough time here, the problem is:
I have a table with manufacturers, applications, and versions, computers.
I need to find out what the max version of an application is on a computer. Where i am having trouble is that some versions are XP or 2000 Professionsal, neither of which is an integer so i am unable to grab a max version.
When i run this script
select a.editor, a.softname, a.version, count(a.hostname)total,
case when suite = 0 then 'NO'
ELSE 'YES'
END AS Suite
from SLIB_RESULTS a
join (select max(version) as 'Version', editor, softname, hostname from SLIB_RESULTS where hostname in (select distinct hostname from SLIB_RESULTS) group by hostname, editor, softname) b on
a.version = b.version and a.hostname = b.hostname
group by a.editor, a.softname, a.version, a.suite
order by a.editor
I am getting 87000+ versions of Office XP, the problem is that there are only 13000 computers on my network.
I know what i want is difficult, but would be grateful to any pointers or ideas to help me get this moving.
-
Do you miss a
and a.softname = b.softname in the query
select a.editor, a.softname, a.version, count(a.hostname)total,
case when suite = 0 then 'NO'
ELSE 'YES'
END AS Suite
from SLIB_RESULTS a
join (select max(version) as 'Version', editor, softname, hostname from SLIB_RESULTS where hostname in (select distinct hostname from SLIB_RESULTS) group by hostname, editor, softname) b on
a.version = b.version and a.hostname = b.hostname and a.softname = b.softname
group by a.editor, a.softname, a.version, a.suite
order by a.editor
Regards,
gova -
ya i missed it in the post, must have copied the one i was messing with, it was actually included in my query in analyser
-
1. You don't need that distintc in the inner query
2. I am going to guess:
select a.editor
, a.softname
, a.version
, count(a.hostname) total
, case when suite = 0 then 'NO' ELSE 'YES' END AS Suite
from SLIB_RESULTS a
join (
select max(version) as 'Version'
, editor
, softname
, hostname
from
SLIB_RESULTS
group by
hostname, editor, softname
 
b ona.version = b.version
and a.hostname = b.hostname
and a.softname = b.softname
and a.editor = b.editor
group by a.editor, a.softname, a.version, a.suite
order by a.editor
* Noel -
Actuall govinn had it right, i did miss a join. I feel like such a fool, thanks for the help.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply