Return max with multiple date fields

1

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

2

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.

3

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
5

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
6

hi

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

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

image