SQLTeam.com | Weblogs | Forums

Return max with multiple date fields

I need to return the max trans_id below however the dates are not unique within the date columns

Max(trans_id) will not work because of different dates , I tried row_number ()partition by I’d,trans_id order by trans_id desc this does not work

Emp table
Id,date_1,date_2,date_3,trans_id
2,2020-03-04,2020-06-06,2020-09-02,15
2,2012-03-12,2011-04-02,2009-09-04,14
2,2006-09-12,2005-11-06,2001-08-07,33

Expected output

Id,date_1,date_2,date_3,trans_id
2,2006-09-12,2005-11-06,2001-08-07,33. ——33 is the max trans_id

It isn't clear how the dates apply to this issue. Do you want the row that contains the max trans_id? Or do you want the rows that has the max date value - and then the max trans_id from those rows?

Based on your example - a simple row number partitioned by Id ordered by trans_id desc will get you the last row, but obviously there is more to the issue than has been stated.

please at least provide sample data in following format

create table #dj(Id int,date_1 date,date_2 date,date_3 date,trans_id int)

insert into #dj
select 2,'2020-03-04','2020-06-06','2020-09-02',15 union
select 2,'2012-03-12','2011-04-02','2009-09-04',14 union
select 2,'2006-09-12','2005-11-06','2001-08-07',33 

--method 1
;with src
as
(
	select *, 
ROW_NUMBER() OVER(PARTITION BY id ORDER BY trans_id DESC)  as _max
	  from #dj
)
select id, date_1, date_2, date_3, trans_id
  from src where _max = 1

--method 2
select d.* 
from #dj d
join (
		select top 1 Id, max(trans_id) as trans_id
		 from #dj
		 group by Id
   ) d2 on d.trans_id = d2.trans_id
And d.id = d2.id

drop table #dj

hi

why not simply this .. just curious !! if there are duplicate rows .. with ties clause ..

SELECT    
   TOP 1 *
FROM dj
   ORDER BY trans_id desc 

image

1 Like

hi

here is .. top 1 with ties ... ..

SELECT    
   TOP 1 with ties *
FROM dj
   ORDER BY trans_id desc

image