-1

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.

Rhod
  • 1
  • 1
  • Which database system are you using? – Progman Aug 04 '23 at 18:01
  • It’s a backend db for a CRM. Standard setup as far as I know. – Rhod Aug 05 '23 at 00:08
  • You are not as clear as you think you are. To improve your question: Do you want the most recent value of `visitdate` for each `accountid` (as I understand from your first sentence, even if it is not stated that clearly)? Or do you want all the fields for the most recent record (evaluated with `visitdate`) of each `accountid` (as suggested by the fact you included the extra `casereference` field in your queries)? Regarding the expected result: you say you have 1185 distinct accounts in the `FilteredVisit` table? Should it not be the number of records you expect? If not, can you explain why? – Atmo Aug 05 '23 at 00:33
  • @Atmo, yes you are right I havent explained myself clearly enough sorry about that (i'll edit the post). Yes there are 1185 distinct AccountId's, I want the latest date for each accountid some have multiple visits some only one. So I was expecting 1185 as a result of the query and not the two different results I've had. Hence my confusion. Thanks for the feedback/help. – Rhod Aug 07 '23 at 08:26

1 Answers1

0

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().

Atmo
  • 2,281
  • 1
  • 2
  • 21