June 24, 2011 at 5:45 pm
Hi there,
I'm struggling a littel bit with the following query not doing what I think it should be doing. I have a list of configurations (configurationname) that are associated with events that occur multiple times during a given month. I want to create a query that shows only the LAST successful event for a given scan configuration.
SELECT a.configurationname, b.endtime
FROM
jobs b
inner join hosts hst on hst.jobid = b.jobid
inner join configurations a on a.configurationID = b.condigurationid
WHERE (a.configurationname like '%ABC%'
and MONTH(EndTime) = datepart(month, getdate())
GROUP BY Configurationname, b.endtime
ORDER BY ConfigurationName
What I would expect to see is the configuration name appearing only once in the result set. And that appearance would be for the even that occurred most recently. Instead am seeing all the events for each scan configuration being returned.
How can I fix my sql mess to get the results I need? I thought the MAX aggregate function would get me there.
Thanks in advance....
June 24, 2011 at 10:31 pm
I think what you want is to use a CTE that makes use of the ROW_NUMBER() function PARTITION BY configurationname and ORDER BY enddate DESC, and then you will select rows from the CTE where ROW_NUMBER() = 1. Below is some untested code based on your original query. If it is not exactly what you're looking for please provide some sample DDL and DML as well as your desired results so I can build an environment on my side and provide tested code. Please follow the gist of this article if you're unsure about it: http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
WITH cte ( configurationname, endtime, row_num )
AS (
SELECT a.configurationname,
b.endtime,
ROW_NUMBER() OVER ( PARTITION BY a.configurationname ORDER BY endtime DESC )
FROM jobs b
INNER JOIN hosts hst ON hst.jobid = b.jobid
INNER JOIN configurations a ON a.configurationID = b.condigurationid
WHERE (
a.configurationname LIKE '%ABC%'
AND MONTH(EndTime) = DATEPART(month, GETDATE())
)
GROUP BY Configurationname,
b.endtime
ORDER BY ConfigurationName
)
SELECT configurationname,
endtime
FROM cte
WHERE row_num = 1 ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 25, 2011 at 11:35 pm
Your sample worked. Thank you very much for the guidance.....I 'get' what is happening in your approach and will include it in my bag of tricks going forward.
CK
June 26, 2011 at 7:58 am
You're welcome 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply