Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Skylizard
Starting Member
2 Posts |
 Posted - 2010-03-18 : 11:53:00
|
| Hi, I've done a query which is quite working and and could use a little advice and a once over. In short it's a query that first provides data, and then a subset query that filters the data on last logged on time. I know i'm missing some thing but if some has some time to quickly look over and advise that would be much appreciated. SELECT Distinct v_R_System.Name0 AS 'Netbios Name', v_R_System.User_Name0 as 'User Name', v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS 'Last logged On', v_R_System.AD_Site_Name0 AS 'AD Site', v_GS_Advanced_Desktop_Moni0.MonitorManufacturer0 AS 'Monitor Manufacturer', v_GS_Advanced_Desktop_Moni0.SerialNumber0 AS 'Serial Number', v_GS_Advanced_Desktop_Moni0.MonitorType0 AS 'Monitor Type', v_GS_Advanced_Desktop_Moni0.DisplayAdapterDriverVersion0 AS 'Display Adapter Driver Version', v_R_System.Creation_Date0 AS 'Creation Date', v_GS_OPERATING_SYSTEM.Caption0 as 'OS', v_GS_OPERATING_SYSTEM.CSDVersion0 as 'Service Pack', v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 as 'Total Physical Memory', v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS 'Last Logon Name' FROM v_R_System LEFT JOIN v_GS_Advanced_Desktop_Moni0 ON v_GS_Advanced_Desktop_Moni0.ResourceID = v_R_System.ResourceID LEFT JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID LEFT JOIN v_GS_ACE_Build_Version0 ON v_GS_ACE_Build_Version0.ResourceID = v_R_System.ResourceID LEFT JOIN v_GS_SYSTEM_CONSOLE_USER ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID LEFT JOIN v_GS_X86_PC_MEMORY ON v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID WHERE v_R_System.Name0 like 'xxx%' or v_R_System.Name0 like 'xxx%' ---ORDER BY v_R_System.Name0 JOIN ( SELECT v_R_System.Name0 AS 'Netbios Name', MAX(v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0)AS 'Last logged On' FROM v_R_System LEFT JOIN v_GS_SYSTEM_CONSOLE_USER ON v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_R_System.ResourceID GROUP BY Name0) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-18 : 11:57:43
|
| sorry didnt understand whats the problem you're facing??------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Skylizard
Starting Member
2 Posts |
Posted - 2010-03-18 : 12:35:38
|
| sorry should have put that in there.There query isn't working properly. I'm not getting back the MAX count. It shyould just return a single line but it's returning multiple counts which is what you get if you exclude the MAX query. so it's not group the whole query together only part of it.hope that makes sense |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-18 : 12:37:14
|
| you're not giving any GROUP BY expression neither are you taking the MAX() select, then how do you expect it to give you a single row with MAX value?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
little query help