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]