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)

Answer 3

You can use %vid variable. For example:

SELECT *, %vid FROM (SELECT * FROM Sample.Person) WHERE %vid BETWEEN 5 AND 10

would return rows 5-10 from Sample.Person table.

Documentation.

Discussion on InterSystems Caché developer community.

Answer 4

before down voting!!!! I am just asking if this would work. Its more of a learning on my part based on how I have done this in the past. I may not understand the question and would rather delete or discuss than receive a down vote :)

SELECT *
FROM client_address a
WHERE
a.date_updated = 
(
SELECT max(b.date_updated) FROM client_address b
group by b.Client_id
)