ROW_COUNT Equivalent to Intersystems Cache?

Question

I have a query that I need to run that returns the most recent 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 in Intersystems cache? I do not find documentation for any ranking function.

Answer 1

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 2

I looked through the documents and is not one of the Window functions that exist in SQL Server, Oracle, or Postgres, so you are stuck in 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 3

You can use the %vid variable. For instance:

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

will return rows 5-10 from the Sample.Person table.

Documentation.

Discussion of the InterSystems Caché developer community.

Answer 4

before voting !!!! I'm just asking if this will work. He is taught more on my part, based on how I did this in the past. Perhaps I do not understand this question and would rather delete or discuss than get a vote :)

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