SQL query

Hey

I have this SQL query:

select all SMS_UserMachineIntelligence.NumberOfLogins
            ,SMS_UserMachineIntelligence.MachineResourceName
            ,SMS_UserMachineIntelligence.UniqueUserName from v_UserMachineIntelligence AS SMS_UserMachineIntelligence

Example data:

NumberOfLogins   MachineResourceName UniqueUserName
47      TPC72039      fasa
1      TPC72039      rilv
14      TPC72557      xynge
3      TPC72557      dddd
140      BB21229            ana
60      BB21229            pana

I need to change the SQL to return - only the MachineResouceName with highest NumberOfLogins:

47      TPC72039      fasa
14      TPC72557      xynge
140      BB21229            ana

Thanks in advance

Mike
LVL 1
mikeydkAsked:
Who is Participating?
 
NerdsOfTechTechnology ScientistCommented:
typo sorry:

select t1.NOL, umi.MachineResourceName, umi.UniqueUserName
FROM v_UserMachineIntelligence umi
INNER JOIN
(SELECT MAX(NumberOfLogins) AS NOL, MachineResourceName
FROM v_UserMachineIntelligence
GROUP BY MachineResourceName) t1
ON  t1.NOL = umi.NumberOfLogins AND t1.MachineResourceName = umi.MachineResourceName

Open in new window

0
 
Bill PrewCommented:
Give this a try:

select Max(SMS_UserMachineIntelligence.NumberOfLogins) AS NumberOfLogins
       ,SMS_UserMachineIntelligence.MachineResourceName
       ,SMS_UserMachineIntelligence.UniqueUserName 
from v_UserMachineIntelligence AS SMS_UserMachineIntelligence 
group by SMS_UserMachineIntelligence.MachineResourceName
       ,SMS_UserMachineIntelligence.UniqueUserName 
order by SMS_UserMachineIntelligence.MachineResourceName
       ,SMS_UserMachineIntelligence.UniqueUserName 

Open in new window


»bp
0
 
NerdsOfTechTechnology ScientistCommented:
select t1.NOL, umi.UniqueUserNam, umi.UniqueUserName
FROM v_UserMachineIntelligence umi
INNER JOIN
(SELECT MAX(NumberOfLogins) AS NOL, MachineResourceName
FROM v_UserMachineIntelligence
GROUP BY MachineResourceName) t1
ON  t1.NOL = umi.NumberOfLogins AND t1.MachineResourceName = umi.MachineResourceName

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mikeydkAuthor Commented:
Bill Prew> Still getting multiple lines with the same MachineResourceName
0
 
Bill PrewCommented:
Still getting multiple lines with the same MachineResourceName

Doh, silly me.  Yes, I read it slightly simpler than you meant it, my fault.

Looks like you have plenty of other options now, I'm sure you'll get what you need by the time I could adjust.


»bp
0
 
mikeydkAuthor Commented:
select t1.NOL, umi.MachineResourceName, umi.UniqueUserName
FROM v_UserMachineIntelligence umi
INNER JOIN
(SELECT MAX(NumberOfLogins) AS NOL, MachineResourceName
FROM v_UserMachineIntelligence
GROUP BY MachineResourceName) t1
ON  t1.NOL = umi.NumberOfLogins AND t1.MachineResourceName = umi.MachineResourceName
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.