Well so far my query returns tenant name implementation name and process name and other fields of transprocesses. there is a date time column in trans_processes whose name is StartDate. So there are multiple process name for tenant name or implementation name. i want to filter out the latest record on the basis of start date in transprocess it should return one value for each tenant name or implementation name, my query so far is
select mi.Name as TenantName , im.ImplementationName as ImplementationName, mc.ColorHexCode as colorcode, tp .* from Master_Instances mi
inner join Mapping_Links_Clients_Instances mlci on mi.Id= mlci.Instance_ID
inner join Trans_Processes tp on mlci.Id= tp.Mapping_Link_Client_Instance
inner join Implementation im on im.MappingId =mlci.Instance_ID
inner join Master_Clients mc on mc.Id= mlci.Client_ID
Table Structure after excution of the above query:
tenant name | implementation name| color code| id | Name | start date
Abottt | Abottt | #000000 | 1902| Abbott 03012022 1| 2022-03-01 15:51:29.213
Abottt | Abottt | #000000 | 1910| Abbott 030222 1| 2022-03-02 15:51:29.213
Abottt | Abottt | #000000 | 1953| Abbott 031022 1| 2022-03-03 15:51:29.213
i want to take out the last record for Abott similiarly there are other records and for each tenant i want to take out latest for all
