Caimen Caimen - 3 months ago 9
SQL Question

How do I find the latest row of a datetime up to the minute only?

I have the following basic SQL statement.

SELECT
[Cycle_Id], [Machine], [Machine_Action], [Cycle_Date]
FROM
[EKAM].[dbo].[Machine_Cycle]
WHERE
Machine = 'OKK 2'
AND Cycle_Date BETWEEN '2016-08-15 7:59:00' AND '2016-08-18 7:59:00'
ORDER BY
Cycle_Date DESC


It returns the following data.

Cycle_Id Machine Machine_Action Cycle_Date
--------------------------------------------------------------
85220 OKK 2 UP 2016-08-15 09:07:39.883
85221 OKK 2 DOWN 2016-08-15 09:08:04.367
85223 OKK 2 UP 2016-08-15 09:08:44.367
85226 OKK 2 DOWN 2016-08-15 09:08:55.367
85233 OKK 2 UP 2016-08-15 09:09:38.367
85234 OKK 2 DOWN 2016-08-15 09:10:07.367


However I need it to pull the following data instead. The main difference being I only want the latest row per minute.

85220 OKK 2 UP 2016-08-15 09:07:39.883
85226 OKK 2 DOWN 2016-08-15 09:08:55.367
85233 OKK 2 UP 2016-08-15 09:09:38.367
85234 OKK 2 DOWN 2016-08-15 09:10:07.367


I have too much data and I'm not worried about data as fine as milliseconds and seconds. I only want one row per minute, but I'm having trouble wrapping my head around this for some reason. Perhaps I just didn't get enough sleep last night.

I feel like I need to recast the Cycle_Date to get rid of seconds and milliseconds, but where do I go from there?

Answer

You shouldn't use BETWEEN here as that will exclude almost all of the last minute which probably isn't what you want.

To just preserve the latest row per minute you can use

WITH T AS
(
SELECT 
   Cycle_Id
  ,[Machine]
  ,[Machine_Action]
  ,[Cycle_Date]
  ,ROW_NUMBER() OVER (PARTITION BY DATEDIFF(Minute, 0, CycleDate) 
                      ORDER BY CycleDate DESC, Cycle_Id DESC) AS RN
FROM [EKAM].[dbo].[Machine_Cycle]
WHERE Machine = 'OKK 2' AND 
Cycle_Date >= '2016-08-15 7:59:00' AND Cycle_Date < '2016-08-18 8:00:00'
)
SELECT 
   Cycle_Id
  ,[Machine]
  ,[Machine_Action]
  ,[Cycle_Date]
FROM T
WHERE RN = 1
ORDER BY Cycle_Date DESC
Comments