Hi Denis,
First of all, it is always a good idea to post DDL (in other words scripts to put the table structures together) and a set of sample data (as INSERT statements) that illustrate the problem you are struggling with. Makes it just so much easier for us volunteers to get to a point to help you without having to put this together ourselves.
Anyhow, seeing that you are quite a new visitor, I've done this for you like such:
-- Sample table declaration
DECLARE @Versions TABLE
(
MachineId int NOT NULL,
AppName varchar ( 255 ) NOT NULL,
ApplicationInfo varchar ( 255 ) NOT NULL
);
-- Add some sample data
INSERT INTO @Versions
(
MachineId,
AppName,
ApplicationInfo
)
VALUES
( 1, 'MSOffice2010', 'MSOffice2010/Alpha' ),
( 1, 'MSProject2010', 'MSProject2010/Beta' ),
( 1, 'MSVisio2010', 'MSVisio2010/Prod' ),
( 1, 'MSVisio2010', 'MSVisio2010/Beta' ),
( 2, 'MSOffice2010', 'MSOffice2010/Alpha' ),
( 2, 'MSOffice2010', 'MSOffice2010/Beta' ),
( 2, 'MSVisio2010', 'MSVisio2010/Prod' ),
( 3, 'MSOffice2010', 'MSOffice2010/Alpha' ),
( 3, 'MSOffice2010', 'MSOffice2010/Beta' ),
( 3, 'MSOffice2010', 'MSOffice2010/Prod' ),
( 3, 'MSVisio2010', 'MSVisio2010/Alpha' ),
( 3, 'MSVisio2010', 'MSVisio2010/Prod' )
-- Check what we have inserted
SELECT
MachineId,
AppName,
ApplicationInfo
FROM @Versions
ORDER BY
MachineId,
AppName,
ApplicationInfo
This is the result:
MachineIdAppNameApplicationInfo
1MSOffice2010MSOffice2010/Alpha
1MSProject2010MSProject2010/Beta
1MSVisio2010MSVisio2010/Beta
1MSVisio2010MSVisio2010/Prod
2MSOffice2010MSOffice2010/Alpha
2MSOffice2010MSOffice2010/Beta
2MSVisio2010MSVisio2010/Prod
3MSOffice2010MSOffice2010/Alpha
3MSOffice2010MSOffice2010/Beta
3MSOffice2010MSOffice2010/Prod
3MSVisio2010MSVisio2010/Alpha
3MSVisio2010MSVisio2010/Prod
Now, you will see that I have just created all three scenarios that you are talking about. Machine 1 has two versions of Visio, Machine 2 has two version of Office, and Machine 3 has 3 versions of Office and 2 versions of Visio.
The next challenge is to figure out which machine has more than one version of which application installed. In order to do that I added a computed column to the table, that extracts the version from the ApplicationInfo column like this:
DECLARE @Versions TABLE
(
MachineId int NOT NULL,
AppName varchar ( 255 ) NOT NULL,
ApplicationInfo varchar ( 255 ) NOT NULL,
AppVersion AS SUBSTRING ( ApplicationInfo, CHARINDEX ( '/', ApplicationInfo ) + 1, LEN ( ApplicationInfo ) )
);
This works of course only if the version is always separated from the application name by a slash. BTW, storing the data like that is not a good idea, because you are violating third normal form, in other words (under the assumption that MachineId and AppName are a key to the table), ApplicationInfo contains part of that key. In your setup, you could have a scenario where AppName = 'MSSQLFeelsGood', but ApplicationInfo = 'OracleFeelsEvenBetter/Prod'. So I'd recommend getting rid of the AppName in ApplicationInfo. The correct primary key would consist of MachineId, AppName and AppVersion, which makes it unique. After all you can't have two Prod versions of MSOffice installed on the same machine, can you?
Anyhow, with the computed column in place we can figure out which machines have more than one version of a given AppName installed as such:
SELECT
MachineId,
AppName
FROM @Versions
GROUP BY
MachineId,
AppName
HAVING
COUNT(*) > 1
ORDER BY
MachineId,
AppName
Which gives the following results:
MachineIdAppName
1MSVisio2010
2MSOffice2010
3MSOffice2010
3MSVisio2010
To get to the result you are after, all we need to do is to join that result set back to the @Versions table, getting the MAX(AppVersion) per MachineId and AppName. This only works, because sorting alphabetically, Alpha comes before Beta (yeah, those ancient Greeks were clever) and Beta comes before Prod (don't ask me who came up with that):
SELECT
V.MachineId,
V.AppName,
MAX(V.AppVersion) AS AppVersion
FROM @Versions V
JOIN
(
SELECT
MachineId,
AppName
FROM @Versions
GROUP BY
MachineId,
AppName
HAVING
COUNT(*) > 1
) G ON G.MachineId = V.MachineId AND G.AppName = V.AppName
GROUP BY
V.MachineId, V.AppName
ORDER BY
V.MachineId, V.AppName
The result looks like this:
MachineIdAppNameAppVersion
1MSVisio2010Prod
2MSOffice2010Beta
3MSOffice2010Prod
3MSVisio2010Prod
Simple, isn't it? All you need to really do is to split the problem into small steps. Build from the bottom up and grow the tree on top of the roots you've layed.
Regards,
Jan