If multiple records come, get row with latest date-CodePudding

I have a table

Admission_Letter_Log_ID | URL    |  Letter_Generation DateTime |   Admission Letter Template ID
       97               some URL     2021-10-08 06:46:53.567         23
       98               some URL     2021-10-08 06:50:03.887         23
       99               some URL     2021-10-08 06:53:29.857         24
       100              some URL     2021-10-08 06:54:54.303         25

I want to get Admission_Letters but if they have same Admission Letter Template ID, return one with the latest Letter generation Date time.

What I have tried:

select Admission_Letter_Template_ID ,max(Letter_Generation_Datetime) FROM 

CoC_Admission_Letter_Log ccall 
where Admission_Application_Master_ID=4487
group by Admission_Letter_Template_ID

Which returns:

Admission_Letter_Template_ID|                       |
---------------------------- ----------------------- 
                          23|2022-08-03 07:38:53.087|
                          27|2022-08-03 07:41:32.287|

It works fine because similar Admission Letter Template ID are returning one with latest Date time but I also want Admission_Letter_Log_ID so instead I try this:

select Admission_Letter_Log_ID,Admission_Letter_Template_ID ,max(Letter_Generation_Datetime) FROM CoC_Admission_Letter_Log ccall 
where Admission_Application_Master_ID=4487
group by Admission_Letter_Log_ID,Admission_Letter_Template_ID

which returns:

Admission_Letter_Log_ID|Admission_Letter_Template_ID|                       |
----------------------- ---------------------------- ----------------------- 
                    116|                          23|2022-08-02 06:32:46.990|
                    117|                          23|2022-08-03 07:38:53.087|
                    118|                          27|2022-08-03 07:41:32.287|

But now my Admission Letter Template ID again are coming as duplicate, instead of returning duplicate ones as one with max dateTime.

TLDR; I want to return this

Admission_Letter_Log_ID|Admission_Letter_Template_ID|                       |
----------------------- ---------------------------- ----------------------- 
                    117|                          23|2022-08-03 07:38:53.087|
                    118|                          27|2022-08-03 07:41:32.287|

CodePudding user response：

You get both rows because the value for Admission_Letter_Log_ID differs in both rows, just like with Letter_Generation_Datetime

So, remove Admission_Letter_Log_ID from the group by, and also use the max function on Admission_Letter_Log_ID

select max(Admission_Letter_Log_ID) as Admission_Letter_Log_ID,
       Admission_Letter_Template_ID,
       max(Letter_Generation_datetime) as Letter_Generation_Datetime
FROM   ccall 
where  Admission_Application_Master_ID = 4487
group by Admission_Letter_Template_ID

See this DBFiddle

Admission_Letter_Log_ID	Admission_Letter_Template_ID	Letter_Generation_Datetime
117	23	2021-10-08 06:50:03.887
118	27	2022-08-03 07:41:32.287

EDIT

As commented by @ThorstenKettner I assumed that a greater Letter_Generation_datetime always means a greater Admission_Letter_Log_ID.
If this is not the case, than another approach is to subquery the grouping and then join on that result, see this query

select c2.Admission_Letter_Log_ID,
       t.Admission_Letter_Template_ID,
       t.Letter_Generation_Datetime
from  ( select c.Admission_Letter_Template_ID,
               max(c.Letter_Generation_datetime) as Letter_Generation_Datetime
        from   ccall c
        where  Admission_Application_Master_ID = 4487
        group by Admission_Letter_Template_ID 
     ) t
  join ccall c2 on t.Admission_Letter_Template_ID = c2.Admission_Letter_Template_ID
               and t.Letter_Generation_Datetime = c2.Letter_Generation_Datetime

And also see this new DBFiddle

CodePudding user response：

try this with sub query

select * from ccall where Admission_Letter_Log_ID in (select Max(ct.Admission_Letter_Log_ID)
FROM   ccall ct
where  ct.Admission_Application_Master_ID = 4487 
group by ct.Admission_Letter_Template_ID 
)

CodePudding user response：

If multiple records come, get row with latest date

This means you don't want to aggregate your data, but simply filter it, i.e. pick the rows to select. One way to do this is by using a window function:

select 
  admission_letter_log_id, url, letter_generation_datetime, admission_letter_template_id
from
(
  select
    coc_admission_letter_log.*,
    max(letter_generation_datetime)
      over (partition by admission_letter_template_id) as max_dt
  from coc_admission_letter_log
  where admission_application_master_id = 4487
) ccall
where letter_generation_datetime = max_dt
order by admission_letter_template_id;

Or:

select top (1) with ties *
from coc_admission_letter_log
where admission_application_master_id = 4487
order by row_number() over (partition by admission_letter_template_id
                            order by letter_generation_datetime desc)