December 27, 2013 at 7:24 am
Hi,
I have a table which includes servernames and status .
CREATE TABLE #Time (Date DATETIME,Status VARCHAR(10),ServerName VARCHAR(10))
INSERT INTO #Time (Date,Status,ServerName) VALUES ('2013-12-01 09:00:02.347','ERROR','Server A')
INSERT INTO #Time (Date,Status,ServerName) VALUES ('2013-12-01 09:57:11.623','SUCCESS','Server A')
INSERT INTO #Time (Date,Status,ServerName) VALUES ('2013-12-01 09:45:42.623','SUCCESS','Server A')
INSERT INTO #Time (Date,Status,ServerName) VALUES ('2013-12-01 09:57:11.623','SUCCESS','Server A')
SELECT DISTINCT MAX(Date) AS MaxTime,Status,ServerName
FROM #Time WHERE Date >= '12/1/2013' AND Date < '12/2/2013'
AND ServerName = 'Server A'
AND DATEPART(hh, Date) = 9
GROUP BY Status,ServerName
DROP TABLE #Time
As of now it returns both the rows below :
MaxTime Status ServerName
2013-12-01 09:00:02.347ERROR Server A
2013-12-01 09:57:11.623SUCCESS Server A
I want to return only this
MaxTime Status ServerName
2013-12-01 09:57:11.623SUCCESS Server A
Thanks,
PSB
December 27, 2013 at 7:39 am
Try using a ranking function.
with C1 as (
select *, rank() over(partition by servername order by [date] DESC) as rnk
from #Time
)
select *
from C1
where rnk = 1;
Replace the "*" with the list of columns you need.
I do not know if there could be ties by [date] so I decided to return ties.
Having an index like "(servername, [date] DESC) include (...)" could hlp with the performance.
December 27, 2013 at 8:19 am
Thanks That worked!
December 28, 2013 at 4:25 am
Here are two more alternatives:
more than one Server:
;
WITH cte AS
(
SELECT DISTINCT ServerName FROM #Time
)
SELECT sub.MaxTime, sub.Status, sub.ServerName
FROM cte
CROSS APPLY
(
SELECT TOP 1 t.Date AS MaxTime,t.Status,t.ServerName
FROM #Time t
WHERE t.ServerName=cte.ServerName
AND Date >= '12/1/2013' AND Date < '12/2/2013'
AND DATEPART(hh, Date) = 9
ORDER BY t.Date DESC
) sub
just a single ServerName:
SELECT TOP 1 Date AS MaxTime,Status,ServerName
FROM #Time WHERE Date >= '12/1/2013' AND Date < '12/2/2013'
AND ServerName = 'Server A'
AND DATEPART(hh, Date) = 9
ORDER BY Date DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply