Oliver Oliver - 1 month ago 6
SQL Question

Find most recent record by date

This is my original data (anonymised):

id usage verified date
1 4000 Y 2015-03-20
2 5000 N 2015-06-20
3 6000 N 2015-07-20
4 7000 Y 2016-09-20


Original query:

SELECT
me.usage,
mes.verified,
mes.date
FROM
Table1 me,
Table2 mes,
Table3 m,
Table4 mp
WHERE
me.theFk=mes.id
AND mes.theFk=m.id
AND m.theFk=mp.id


How would I go about selecting the most recent verified and non-verified?

So I would be left with:

id usage verified date
1 6000 N 2015-07-20
2 7000 Y 2016-09-20


I am using Microsoft SQL Server 2012.

Answer

First, do not use implicit joins. This was discontinued more than 10 years ago.

Second, embrace the power of the CTE, the in clause and row_number:

with CTE as
(
select 
  me.usage,
  mes.verified,
  mes.date,
  row_number() over (partition by Verified order by Date desc) as CTEOrd
from Table1 me
inner join Table2 mes
  on me.theFK = mes.id
where mes.theFK in
  (
  select m.id
  from Table3 m
  inner join Table4 mp
    on mp.id = m.theFK
  )
)
select CTE.*
from CTE
where CTEOrd = 1
Comments