intersystems-cache

ROW_COUNT Equivalent for Intersystems Cache?

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.

Solution

  • 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)