SELECT CUSTOMER_ID, AMOUNT, UPDATE_DATE, UPDATE_TIME
FROM (
SELECT *, ROW_NUMBER() OVER(PARITITION BY CUSTOMER_ID
ORDER BY UPDATE_DATE DESC, UPDATE_TIME DESC) AS row_num
FROM dbo.CUSTOMER_DATA
) AS derived
WHERE row_num = 1
--Edit: Added UPDATE_TIME to the ORDER BY, based on harishgg1's comments
Scotts reply is not fetching the correct results .. please excuse me Scott
The SQL I wrote below gives the correct result
create data script
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CUSTOMER_DATA]') AND type in (N'U'))
DROP TABLE [dbo].[CUSTOMER_DATA]
GO
/****** Object: Table [dbo].[CUSTOMER_DATA] Script Date: Sunday Feb 26 2023 1:55:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CUSTOMER_DATA](
[CUSTOMER_ID] [int] NULL,
[AMOUNT] [int] NULL,
[UPDATE_DATE] [int] NULL,
[UPDATE_TIME] time NULL
) ON [PRIMARY]
GO
INSERT [dbo].[CUSTOMER_DATA] ([CUSTOMER_ID], [AMOUNT], [UPDATE_DATE], [UPDATE_TIME]) VALUES (1001, 1000, 20151030, CAST(N'18:00:00' AS Time))
GO
INSERT [dbo].[CUSTOMER_DATA] ([CUSTOMER_ID], [AMOUNT], [UPDATE_DATE], [UPDATE_TIME]) VALUES (1001, 15000, 20151230, CAST(N'17:00:00' AS Time))
GO
INSERT [dbo].[CUSTOMER_DATA] ([CUSTOMER_ID], [AMOUNT], [UPDATE_DATE], [UPDATE_TIME]) VALUES (1003, 1500, 20151130, CAST(N'15:00:00' AS Time))
GO
INSERT [dbo].[CUSTOMER_DATA] ([CUSTOMER_ID], [AMOUNT], [UPDATE_DATE], [UPDATE_TIME]) VALUES (1003, 1000, 20151230, CAST(N'18:30:00' AS Time))
GO
INSERT [dbo].[CUSTOMER_DATA] ([CUSTOMER_ID], [AMOUNT], [UPDATE_DATE], [UPDATE_TIME]) VALUES (1005, 1600, 20151230, CAST(N'19:00:00' AS Time))
GO
INSERT [dbo].[CUSTOMER_DATA] ([CUSTOMER_ID], [AMOUNT], [UPDATE_DATE], [UPDATE_TIME]) VALUES (1005, 1700, 20151230, CAST(N'19:30:00' AS Time))
GO
; with cte as
(
select
*
, row_number() over(partition by Customer_ID order by CONVERT(datetime, convert(varchar(10), update_date))+cast(UPDATE_TIME as datetime) desc ) as rn
from CUSTOMER_DATA
)
select * from cte where rn = 1
@Amir826 ,
Though it won't make any difference in this case, what are the datatypes for the columns? I ask only to make you aware that it COULD actually make a difference.
Since this is your first post on this forum, I recommend that you post your sample data as "Readily Consumable" data. You'll be removing all doubts and you'll help the people that are trying to help you if you take just a little more time with your post to post the example data in a manner similar to the following...
--===== Conditionally drop and create a temporary test table.
DROP TABLE IF EXISTS #CUSTOMER_DATA;
GO
--===== Create the test table with the datatypes and NULLability that you're actually using.
CREATE TABLE #CUSTOMER_DATA
(
CUSTOMER_ID int NOT NULL
,AMOUNT int NOT NULL
,UPDATE_DATE date NOT NULL
,UPDATE_TIME time(0) NOT NULL
)
;
--===== Populate the test table instead of just listing the data.
INSERT INTO #CUSTOMER_DATA
(CUSTOMER_ID, AMOUNT, UPDATE_DATE, UPDATE_TIME)
VALUES (1001, 1000,'20151030','18:00')
,(1001,15000,'20151230','17:00')
,(1003, 1500,'20151130','15:00')
,(1003, 1000,'20151230','18:30')
,(1005, 1600,'20151230','19:00')
,(1005, 1700,'20151230','19:30')
;