Home > Back-end >  How find maximum time with grouping
How find maximum time with grouping

Time:03-24

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] 

How can I fix the request?

CodePudding user response:

SELECT [seq_id],[seq_wf],MAX([update_ts])
FROM [dbo].[#tmp_time] 
GROUP BY [seq_id],[seq_wf]

CodePudding user response:

Try this:

SELECT 
    [seq_id], [seq_wf], MAX([update_ts]) [update_ts]
FROM [dbo].[#tmp_time] 
GROUP BY [seq_id], [seq_wf]
  • Related