Top from the group SQL query in Caché
Hi!
There is interesting question in Stackoverflow.
This query works for MS SQL:
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
There is one answer there, which solves the task:
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
But it goes with comment, that there are no Window functions in Caché SQL.
I wonder, are there any other options to make this query in Caché?