How to build subquery on aggregate query

S

SriramNA

I have a summary result based on the following query:

SELECT Altiris.ComputerName, Altiris.UserID, count(*) AS UsedCount,
MAX(LastExecute) AS LastUsed
FROM Altiris
GROUP BY Altiris.ComputerName, Altiris.UserID;

I get results such as:
ComputerName UserID UsedCount LastUsed
022_10_PC1 HGS2OKL 3 2-29-12 8:30 AM
022_10_PC1 HKM2OKL 2 5-30-12 1:48 PM
022_10_PC2 HKM2OKL 3 5-30-12 11:50 AM
022_10_PC2 MDC2OKL 3 3-26-12 2:40 PM
LUB01234 PLR2LUB 5 5-30-12 8:03 AM
LUB02010 CEA1LUB 5 5-31-12 2:05 PM
LUB04173 PIS2LUB 4 5-30-12 12:23 PM
LUB04222 TRR1LUB 7 5-30-12 8:20 AM
LUB04444 PHR1LUB 5 5-30-12 9:04 AM
LUB04484 CEA1LUB 3 12-28-11 5:48 PM
LUB04484 PUM1PU4 3 11-29-11 5:48 PM
LUB04490 CEA1LUB 4 2-20-12 12:32 PM
LUB04503 CEA1LUB 1 12-16-11 4:18 PM
LUB04503 SHD3LUB 2 4-11-12 2:16 PM
LUB04517 DUN3LUB 5 5-30-12 2:16 PM


I need to identify the most likely user as the one who has used the computer
the most number of times. Where there is a tie, the decision is to be taken
based on the user who used it last.

How do I build the SQL subquery to list these unique combinations of
ComputerName and UserID?

The result would be something like:
022_10_PC1 HGS2OKL 3 2-29-12 8:30 AM
022_10_PC2 HKM2OKL 3 5-30-12 11:50 AM
LUB01234 PLR2LUB 5 5-30-12 8:03 AM
LUB02010 CEA1LUB 5 5-31-12 2:05 PM
LUB04173 PIS2LUB 4 5-30-12 12:23 PM
LUB04222 TRR1LUB 7 5-30-12 8:20 AM
LUB04444 PHR1LUB 5 5-30-12 9:04 AM
LUB04484 CEA1LUB 3 12-28-11 5:48 PM
LUB04490 CEA1LUB 4 2-20-12 12:32 PM
LUB04503 SHD3LUB 2 4-11-12 2:16 PM
LUB04517 DUN3LUB 5 5-30-12 2:16 PM
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top