0

I am trying to get the latest data records against each id ( the status may vary but we just need the status of the latest date )

RoeNumer() and Max(date) are not compatible:

Eg:

Thats an example

I have tried

Select a.Casedep,b.id,b.Max(date),b.Status from table1 as a inner join tabe2 as b on a.id =b.id

and

Select a.Casedep,b.id,b.date,b.Status from table1 as a inner join tabe2 as b on a.id =b.id inner join (Select Max(EventTime) from table2 where b3.id = b.id) as b3 on b.id= b3.id

and Row_Number() and cross apply doesn't work as its old version of SQL

The result I am getting at this time is one with all values of status and date.

Radhika
  • 1

2 Answers2

0

You can use row_number analytical function as follows:

Select * from
(Select a.Casedep,b.id,b.date,b.Status, 
       row_number() over (partition by b.id order b.eventtime desc) as rn
   from table1 as a inner join tabe2 as b on a.id =b.id)
Where rn=1

If row_number is not compatible then you can use not exists as follows:

Select a.Casedep,b.id,b.date,b.Status
  from table1 as a inner join tabe2 as b on a.id =b.id
Where not exists (select 1 from table2 t3
   Where t3.id = b.id
     And t3.eventtime > b.eventtime)
Popeye
  • 35,427
  • 4
  • 10
  • 31
0

You can use the below query

select distinct Table1.id, Table1.case_desp, Table2.Status, Table2.date_v
from Table1 inner join Table2 on Table1.id = Table2.id
inner join (select id, max(date_v) date_v from table2 group by id) c
  on Table1.id = c.id  and Table2.date_v = c.date_v

SqlFiddleLink - http://sqlfiddle.com/#!9/2ab815/7/0

Prasanna
  • 2,390
  • 10
  • 11