→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
laurin1
Engaged Sweeper III
I am trying to get all of the machines that are not at the highest version of Windows 10. I get get the highest version:

Select Max(tblOperatingsystem.Version)
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption Like '%Windows 10%'

And I get all machines < a specific version:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblOperatingsystem.Version,
tblAssets.Lastseen
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption Like '%Windows 10%' And
tblOperatingsystem.Version < '10.0.14393'
Order By tblOperatingsystem.Version

This works in MySQL, but apparently not in (SQLlite?):

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblOperatingsystem.Version,
tblAssets.Lastseen
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption Like '%Windows 10%' And
tblOperatingsystem.Version < (Select Max(tblOperatingsystem.Version)
From tblAssets Inner Join tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption Like '%Windows 10%')
Order By tblOperatingsystem.Version
1 ACCEPTED SOLUTION
laurin1
Engaged Sweeper III
I figured out how to do it using a left join:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblOperatingsystem.Version,
tblAssets.Lastseen
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join (Select Max(tblOperatingsystem.Version) version
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption Like '%Windows 10%') maxv
On maxv.version = tblOperatingsystem.Version
Where tblOperatingsystem.Caption Like '%Windows 10%' And maxv.version Is Null
Order By tblOperatingsystem.Version

View solution in original post

1 REPLY 1
laurin1
Engaged Sweeper III
I figured out how to do it using a left join:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblOperatingsystem.Version,
tblAssets.Lastseen
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join (Select Max(tblOperatingsystem.Version) version
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption Like '%Windows 10%') maxv
On maxv.version = tblOperatingsystem.Version
Where tblOperatingsystem.Caption Like '%Windows 10%' And maxv.version Is Null
Order By tblOperatingsystem.Version

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now