How to take the most updated record from a table by UPDATE_DATE and UPDATE_TIME

Hello,

I have this table:

CUSTOMER_DATA:

CUSTOMER_ID AMOUNT UPDATE_DATE UPDATE_TIME
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

and i want to bring in my results the most updated record for every customer:

CUSTOMER_ID AMOUNT UPDATE_DATE UPDATE_TIME
1001 15000 20151230 17:00
1003 1000 20151230 18:30
1005 1700 20151230 19:30

Can anyone help with this?


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

Thanks!

hi

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

1 Like

@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')
;