How find maximum time with grouping

Question

I've the following table (my_data):

CREATE TABLE [dbo].[#tmp_time](
    [seq_id] int NULL,
    [seq_wf] int NULL,
    [update_ts] [datetime] NULL
) ON [PRIMARY]
INSERT INTO [dbo].[#tmp_time] ([seq_id],[seq_wf],[update_ts]) VALUES (230000,23,'20220319 10:02:33')
INSERT INTO [dbo].[#tmp_time] ([seq_id],[seq_wf],[update_ts]) VALUES (230001,23,'20220319 10:28:33')
INSERT INTO [dbo].[#tmp_time] ([seq_id],[seq_wf],[update_ts]) VALUES (230002,23,'20220319 10:37:33')
INSERT INTO [dbo].[#tmp_time] ([seq_id],[seq_wf],[update_ts]) VALUES (230000,23,'20220319 18:02:33')
INSERT INTO [dbo].[#tmp_time] ([seq_id],[seq_wf],[update_ts]) VALUES (230001,23,'20220319 19:28:33')
INSERT INTO [dbo].[#tmp_time] ([seq_id],[seq_wf],[update_ts]) VALUES (230002,23,'20220319 18:37:33')
INSERT INTO [dbo].[#tmp_time] ([seq_id],[seq_wf],[update_ts]) VALUES (230000,23,'20220320 20:02:33')
INSERT INTO [dbo].[#tmp_time] ([seq_id],[seq_wf],[update_ts]) VALUES (230001,23,'20220320 20:28:33')
INSERT INTO [dbo].[#tmp_time] ([seq_id],[seq_wf],[update_ts]) VALUES (230002,23,'20220321 21:37:33')

I would like to get the result with below values

230002,23,'20220319 10:37:33'
230001,23,'20220319 19:28:33'
230002,23,'20220321 21:37:33'

I use:
select distinct [seq_id]
, max([update_ts]) over (partition by [seq_id]) max_time
from [dbo].[#tmp_time]

Or do I need to number the lines and then find the maximum, but numbering with the results below fails

SELECT [seq_id],[seq_wf],[update_ts],row_number() over(partition BY seq_wf ORDER BY seq_wf) num
FROM [dbo].[#tmp_time] 

230000  23  2022-03-19 10:02:33.000 1
230001  23  2022-03-19 10:28:33.000 1
230002  23  2022-03-19 10:37:33.000 1
230000  23  2022-03-19 18:02:33.000 2
230001  23  2022-03-19 19:28:33.000 2
230002  23  2022-03-19 18:37:33.000 2
230000  23  2022-03-20 20:02:33.000 3
230001  23  2022-03-20 20:28:33.000 3
230002  23  2022-03-21 21:37:33.000 3

How can I fix the request?

Answer 1

Hi @Илья Листратенко
As Naomi said,we need a primary key column to help us divide the rows into sets.
We can add an auto-increment field to #tmp_time table，like this:

ALTER TABLE #tmp_time ADD PK_ID INT IDENTITY(1,1) NOT NULL  

Then check this query:

;WITH CTE AS   
(  
 SELECT (PK_ID-1)/3 +1 AS GROUP_ID,seq_id,seq_wf,update_ts, ROW_NUMBER()OVER(PARTITION BY (PK_ID-1)/3 +1 ORDER BY update_ts DESC)AS RowNum  
 FROM #tmp_time  
)  
SELECT seq_id,seq_wf,update_ts  
FROM CTE  
WHERE RowNum=1  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Do you want to find max time in each set of 3 rows? What field controls the order of the rows you're showing (some extra column)?