MAX date problem I've tried two solutions I've found but the results dont match

Question

I am trying to find the Max date (latest date) for each accountid in my table FilteredVisit. In my table I have the fields: accountid, casereference and visitdate.

I want the latest date for each accountid. Some have multiple visits some only one visit.

I have 2,464 records in the visit table and I know that I have 1,185 unique accounts in that table.

So I'm expecting 1,185 records the result of the query. But having tried the two methods below I get different answers neither of which match the target of 1,185 records.

These are the methods I've tried:

Select accountid, casereference, visitdate
from (Select accountid, casereference, visitdate, 
ROW_NUMBER() over(partition by visitdate order by accountid) as rn from FilteredVisit) as T
where rn = 1
order by hiw_accountid

--which returns 1,215 records

---

SELECT  accountid, casereference, visitdate
FROM FilteredVisit S 
WHERE visitdate=(SELECT MAX(visitdate) FROM Filteredhiw_inspection WHERE hiw_accountid = S.hiw_accountid)
ORDER BY accountid

-- which returns 1,165 records

which solution do I trust? What have I done wrong? Thanks

It’s a backend db for a CRM. Standard setup as far as I know. Thanks for the feedback which has helped update this post.

Answer 1

Either you only want the date for each accountid, in which case you can do:

SELECT  accountid, MAX(visitdate)
FROM FilteredVisit
GROUP BY accountid

That way, you are guaranteed every accountid is returned (guaranteed because there is no filter in a WHERE clause) exactly once (guaranteed by the GROUP BY clause).

---

If you need all the columns, using a ROW_NUMBER() window function is the right approach but you did so incorrectly. You want to partition your records by accountid and inside each partition, you want to apply an order by visitdate that will let you know which one is the latest.
Window functions, as suggested by the fact they work with a partition of your records rather than aggregate them with a group by, do not change the number of records returned by the query, compared to the number of records passed to them. For that, you need to add a WHERE clause to ensure you only keep records you are interested in.

SELECT accountid, casereference, visitdate
FROM (
SELECT accountid, casereference, visitdate,
ROW_NUMBER() OVER (PARTITION BY accountid ORDER BY visitdate DESC) AS IndexVisitDesc
FROM FilteredVisit
) AS T
WHERE IndexVisitDesc = 1

Note that in cases where you anticipate ties within partitions (in the above case, if 2 visits take place at the same visitdate), you may need to use the RANK() or DENSE_RANK() window functions instead or ROW_NUMBER().