January 12, 2010 at 3:30 am
Hi,
I would be very grateful for any help with this as it has proved quite confusing. I am running the following query:
Select MAX(eventtime) as TestDate,
machname
From scriptlog,vmachine,itf_companyID
Where scriptlog.Agentguid = vmachine.agentguid
AND vmachine.groupname = itf_companyID.groupname
AND description like 'Av status%'
AND vmachine.ostype IN ('2003','2000','2008')
AND itf_companyID.company_RecID = 444
Group By machname
It returns the following 6 rows:
TestDate machname
----------------------- ----------------------------------------------------------------------------------------------------
2010-01-07 19:14:22.000 topdubctx02
2010-01-07 19:14:23.000 topdubdc1
2010-01-07 19:14:23.000 topdubdc3
2010-01-07 19:14:25.000 topdubex1
2010-01-07 19:14:24.000 topdubsql02
2010-01-07 19:14:21.000 vc-server
The eventtime column is contained in a table called dbo.scriptlog.
The machname column is contained in a view called dbo.vMachine.
However I need to include another column called description which is also in the table dbo.scriptlog.
I tried the following query, but it returns 12 rows instead of 6:
Select MAX(eventtime) as TestDate,description,machname
From scriptlog,vmachine,itf_companyID
----join tables scriptlog and vmachine
Where scriptlog.Agentguid = vmachine.agentguid
----join tables vmachine and itf_companyID
AND vmachine.groupname = itf_companyID.groupname
AND description like 'Av status%'
AND vmachine.ostype IN ('2003','2000','2008')
AND itf_companyID.company_RecID = 444
Group By description,machname
ORDER BY TestDate
TestDate description machname
---------------------------------------------------------------------------------------------
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 18:04:29 topdubctx02
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:45:57 topdubdc1
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:50:22 topdubdc3
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:49:38 topdubex1
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:48:19 topdubsql02
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:53:59 vc-server
2010-01-07 19:14:21.000 AV Status: Up-to-date 07/01/2010 18:54:03 vc-server
2010-01-07 19:14:22.000 AV Status: Up-to-date 07/01/2010 19:04:29 topdubctx02
2010-01-07 19:14:23.000 AV Status: Up-to-date 07/01/2010 18:45:55 topdubdc1
2010-01-07 19:14:23.000 AV Status: Up-to-date 07/01/2010 18:50:23 topdubdc3
2010-01-07 19:14:24.000 AV Status: Up-to-date 07/01/2010 18:48:30 topdubsql02
2010-01-07 19:14:25.000 AV Status: Up-to-date 07/01/2010 18:49:45 topdubex1
(12 row(s) affected)
In summary, I need to return the eventtime, description and machname columns from these tables
but I want the description and machine names only for rows which have the most recent eventtime value for each particular machine name. Including the description in the group by clause causes this to return 2 rows for each machname instead of the row corresponding to the most recent event time.
I have also been asked by my company not to use temporary tables or table variables when solving this. I am aware that it could possibly be solved with a combination of temporary tables and a cursor but this is not a viable solution in this case.
Could anyone possibly offer a solution to this using subqueries/joins/Common Table Expressions? I have tried many combinations but have been unsuccessful due to the combination of an aggregate function (MAX) and the GROUP BY clause in the query.
Again I would very much appreciate any help on this. Thanks in advance.
January 12, 2010 at 4:09 am
Please provide table definition and sample data as described in the first link in my signature.
Reason: there are some people around here (like me) that prefer to provide tested code.
By providing ready to use data you'll increase the number of people trying to help you.
January 12, 2010 at 4:18 am
I have found a solution using the following simple sql, however it uses a temporary table which I unfortunately have been asked not to use:
USE ksubscribers
GO
Select MAX(eventtime) as TestDate
INTO #TheTestDates
From scriptlog,vmachine,itf_companyID
Where scriptlog.Agentguid = vmachine.agentguid
AND vmachine.groupname = itf_companyID.groupname
AND description like 'Av status%'
AND vmachine.ostype IN ('2003','2000','2008')
AND itf_companyID.company_RecID = 444
Group By machname
ORDER BY TestDate
Select #TheTestDates.TestDate,scriptlog.description,vMachine.machname
From dbo.scriptlog,dbo.vmachine,dbo.itf_companyID,#TheTestDates
Where scriptlog.Agentguid = vmachine.agentguid
AND scriptlog.eventTime=#TheTestDates.TestDate
AND vmachine.groupname = itf_companyID.groupname
AND description like 'Av status%'
AND vmachine.ostype IN ('2003','2000','2008')
AND itf_companyID.company_RecID = 444
DROP TABLE #TheTestDates
This code works and returns what I want. But unfortunately I cannot use it. I have been asked to find a solution using only code, without the aid of table variables or temporary tables. I will upload the table structure and sample data. Thanks for your help.
January 12, 2010 at 5:13 am
Ronan
Run this:
SELECT RowMark = ROW_NUMBER OVER (PARTITION BY machname ORDER BY eventtime),
eventtime, [description], machname
FROM scriptlog s
INNER JOIN vmachine v ON v.Agentguid = s.agentguid
INNER JOIN itf_companyID c ON c.groupname = v.groupname
WHERE [description] LIKE 'Av status%'
AND v.ostype IN ('2003','2000','2008')
AND c.company_RecID = 444
ORDER BY machname, [description], eventtime
Have a look and see if the column RowMark indicates the rows you want to keep. If not, please explain how it differs.
Notice that I've changed your old-style joins to JOIN syntax.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 12, 2010 at 7:02 am
Hi,
The problem is that the description field is diferente, for instance for machine topdubctx02 you get 2 rows in the result:
TestDate description machname
---------------------------------------------------------------------------------------------
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 18:04:29 topdubctx02
2010-01-07 19:14:22.000 AV Status: Up-to-date 07/01/2010 19:04:29 topdubctx02
try this solution and see if it solves your problem:
select LastEvent as TestDate, Description, MachName
FROM scriptlog, vmachine,itf_companyID, (
select Agentguid, MAX(eventtime) as LastEvent
FROM scriptlog
WHERE description like 'Av status%'
GROUP BY Agentguid
) LastEvent
WHERE scriptlog.Agentguid = LastEvent.Agentguid
AND scriptlog.eventtime = LastEvent.LastEvent
AND LastEvent.Agentguid = vmachine.agentguid
AND vmachine.groupname = itf_companyID.groupname
AND vmachine.ostype IN ('2003','2000','2008')
AND itf_companyID.company_RecID = 444
Group By description,machname
ORDER BY LastEvent
I was unable to teste this code.
José Cruz
January 12, 2010 at 7:09 am
José.Cruz (1/12/2010)
Hi,The problem is that the description field is diferente, for instance for machine topdubctx02 you get 2 rows in the result:
TestDate description machname
---------------------------------------------------------------------------------------------
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 18:04:29 topdubctx02
2010-01-07 19:14:22.000 AV Status: Up-to-date 07/01/2010 19:04:29 topdubctx02
José Cruz
Where you have 2 rows in the result, it's because you have 2 different descriptions. Which one would you like to keep, which one would you discard? What are the rules?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 18, 2010 at 10:28 am
Hi there.
Many thanks to all who replied to this post. Your help was much appreciated. I finally found a solution to this. The problem was as follows:
We are trying to return the columns eventtime (aliased to TestDate), machname and description. There are a number of different eventtime values for each machine AND a number of different description values for each machine. Using MAX aggregate function with eventtime and group by with description and machname we were getting too many rows. We solved the issue but only with the help of temporary tables.
ie: we wanted this:
TestDate description machname
2010-01-18 11:41:02.000 AV Status: Up-to-date 18/01/2010 11:34:11 topdubctx02
2010-01-18 11:41:02.000 AV Status: Up-to-date 18/01/2010 11:16:03 topdubdc1
2010-01-18 11:41:02.000 AV Status: Up-to-date 18/01/2010 11:20:35 topdubdc3
2010-01-18 11:41:06.000 AV Status: Up-to-date 18/01/2010 11:21:57 topdubex1
2010-01-18 11:41:03.000 AV Status: Up-to-date 18/01/2010 11:19:15 topdubsql02
2010-01-18 11:41:00.000 AV Status: Up-to-date 18/01/2010 11:24:03 vc-server
(6 row(s) affected)
But were getting this:
TestDate description machname
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:45:57 topdubdc1
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:48:19 topdubsql02
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:49:38 topdubex1
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:50:22 topdubdc3
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 17:53:59 vc-server
2010-01-07 18:07:45.000 AV Status: Up-to-date 07/01/2010 18:04:29 topdubctx02
2010-01-07 19:14:21.000 AV Status: Up-to-date 07/01/2010 18:54:03 vc-server
2010-01-07 19:14:22.000 AV Status: Up-to-date 07/01/2010 19:04:29 topdubctx02
2010-01-07 19:14:23.000 AV Status: Up-to-date 07/01/2010 18:45:55 topdubdc1
2010-01-07 19:14:23.000 AV Status: Up-to-date 07/01/2010 18:50:23 topdubdc3
2010-01-07 19:14:24.000 AV Status: Up-to-date 07/01/2010 18:48:30 topdubsql02
2010-01-07 19:14:25.000 AV Status: Up-to-date 07/01/2010 18:49:45 topdubex1
2010-01-18 11:41:00.000 AV Status: Up-to-date 18/01/2010 11:24:03 vc-server
2010-01-18 11:41:02.000 AV Status: Up-to-date 18/01/2010 11:34:11 topdubctx02
2010-01-18 11:41:02.000 AV Status: Up-to-date 18/01/2010 11:20:35 topdubdc3
2010-01-18 11:41:02.000 AV Status: Up-to-date 18/01/2010 11:16:03 topdubdc1
2010-01-18 11:41:03.000 AV Status: Up-to-date 18/01/2010 11:19:15 topdubsql02
2010-01-18 11:41:06.000 AV Status: Up-to-date 18/01/2010 11:21:57 topdubex1
(18 row(s) affected)
We were originally using the following query:
--USE ksubscribers
--GO
--Select MAX(eventtime) as TestDate,description,machname
--From scriptlog,vmachine,itf_companyID
--Where scriptlog.Agentguid = vmachine.agentguid
--AND vmachine.groupname = itf_companyID.groupname
--AND description like 'Av status%'
--AND vmachine.ostype IN ('2003','2000','2008')
--AND itf_companyID.company_RecID = 444
--Group By machname, description
--ORDER BY TestDate
But I found two solutions:
One using subqueries and temporary tables:
------------------------------------------------------------------------------------------------
Select MAX(eventtime) as TestDate,description,machname
INTO #DataWithMultipleDescriptions
From scriptlog,vmachine,itf_companyID
Where scriptlog.Agentguid = vmachine.agentguid
AND vmachine.groupname = itf_companyID.groupname
AND description like 'Av status%'
AND vmachine.ostype IN ('2003','2000','2008')
AND itf_companyID.company_RecID = 444
Group By machname, description
ORDER BY machname
SELECT TestDate, description, machname
FROM #DataWithMultipleDescriptions
WHERE CONVERT(DATETIME, RIGHT(description, 19),103) in
(
SELECT MAX(CONVERT(DATETIME,RIGHT(description, 19),103))
FROM #DataWithMultipleDescriptions
GROUP BY machname
)
DROP TABLE #DataWithMultipleDescriptions
And one using temporary tables, joins and primary keys:
------------------------------------------------------------------------------------------------
Select Max(scriptlog.scriptlogID) as scriptID,scriptlog.agentguid
INTO #temptable22
From scriptlog,vmachine,itf_companyID
Where scriptlog.Agentguid = vmachine.agentguid
AND vmachine.groupname = itf_companyID.groupname
AND description like 'Av status%'
AND vmachine.ostype IN ('2003','2000','2008')
AND itf_companyID.company_RecID = 444
Group By scriptlog.agentguid
Select scriptlog.description,vmachine.machname,scriptlog.eventtime
from #Temptable22,scriptlog,vmachine
Where #temptable22.agentguid = scriptlog.agentguid
And #temptable22.scriptID = scriptlog.scriptlogID
AND vmachine.agentguid = #temptable22.agentguid
drop table #temptable22
January 18, 2010 at 12:09 pm
I'd like to repeat what I stated before:
Please provide table definition, sample data (in a ready to use format) and expected output as described in the first link in my signature.
There are some folks around here who like to provide tested code.
January 20, 2010 at 2:33 am
Hi Lutz,
Thanks for your input to this post. My previous reply was just to say that we have solved this issue now. It's not ideal because we had to use temporary tables but it is giving us the desired result. My above reply was just explaining how we solved it. It may or may not be of help to other database professionals. I read your article on posting DDL statements to create the tables and other database objects and providing sample data. Unfortunately the tables and views used in this query are quite complicated. I spent well over an hour trying to set up the required DDL statements and sample data for this post but in the end I gave up and went back to working on the problem myself because it was just taking too long to script out all the create table statements and insert statements required to set up a testing environment for the queries we are running. The table and view structure and data are just quite complicated. Anyway, problem solved. For what it's worth my previous post shows two queries we came up with that both do the same thing and solved our issue. Cheers,
Ronan
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply