ROW_COUNT Equivalent for Intersystems Cache?

Question

I have a query I need to run that returns the most recently updated row for each client.

In SQL Server, I would do the following:

SELECT * 
FROM 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date_updated DESC) AS rn
    FROM client_address
) a
WHERE a.rn = 1

Is there a similar way to do this on Intersystems Cache? I'm not finding any documentation for any type of ranking function.

Answer 1

I looked at the docs and the doesn't appear to be any of the Window functions that exist in SQL Server, Oracle or Postgres so you're stuck with the ANTI-THETA-SELF-JOIN solution.

SELECT *
FROM 
   client_address a
   LEFT JOIN client_address b
   on a.client_id  = b.client_id 
     and a.date_updated < b.date_updated 
WHERE
   b.client_id is null

Answer 2

See the documentation for HAVING. Here's how to use it in this case:

SELECT *
FROM client_address
GROUP BY client_id
HAVING date_updated = MIN(date_updated)