max date order by status

Question

Hi Expert,

i wanted to fetch status and max new date , value data

create table test
(newdate date,status varchar(10, value varchar(10))

insert into test
values('01-07-2020','Newone','segmentone'),('07-07-2020','Newone','segmenwo'),('09-07-2020','Newtwo','segmenthee'),('10-07-2020','Newtwo','segmenthee')

expected output

Answer 1

Try:

;WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY [Status] ORDER BY [NewDate] DESC) AS Rn
FROM #test)

SELECT [NewDate], [Status], [Value] FROM cte WHERE Rn = 1;

Answer 2

HI Expert,

This is perfect. but the data needs max(date) should be order by same day and not all calendar days

insert into test

values

('2022-01-04 18:00:57','Newone','segmentone'),('2022-01-04 19:00:57','Newone','segmenwo'),
('2022-04-04 18:00:57','Newtwo','segmenthee'),('2022-04-04 19:00:57','Newtwo','segmenfour')

expected output:

('2022-01-04 19:00:57','Newone','segmenwo'),

('2022-04-04 19:00:57','Newtwo','segmenfour')

Answer 3

You need a datetime data type for what you want.

Answer 4

Hi @Shambhu Rai
First,you need to ensure that the datatype of 'newdate' column is DATETIME.
Then,to get max(date),here are two solutions:
1.Use Row_number()over(Partition by status Order by ...)

;WITH CTE AS  
(  
 SELECT *,ROW_NUMBER()OVER(PARTITION BY status ORDER BY newdate DESC) AS RowNum  
 FROM #test  
)  
SELECT newdate,status,value  
FROM CTE  
WHERE RowNum = 1  

2.Use MAX() along with JOIN:

;WITH CTE AS  
(  
 SELECT MAX(newdate)AS Max_newdate,status  
 FROM #test  
 GROUP BY status  
)  
SELECT C.Max_newdate,C.status,T.value  
FROM CTE C JOIN #test T ON C.Max_newdate=T.newdate AND C.status=T.status  

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 5

Hi Expert,

there is condition when value is same for the status then it should take min date and if the value is change for the status then max date

create table test
(newdate datetime,status varchar(10), value varchar(10))

insert into test

values

('2022-01-04 18:00:57','Newone','segmentone'),('2022-01-04 19:00:57','Newone','segmentone'),
('2022-04-04 18:00:57','Newtwo','segmenthee'),('2022-04-04 19:00:57','Newtwo','segmenfour')

expected output

('2022-01-04 18:00:57','Newone','segmentone')
('2022-04-04 19:00:57','Newtwo','segmentfour')