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é?
This is a good use case for HAVING. Using Sample.Person as an example, the following queries are equivalent:
Implemented with HAVING:
The second is much more intuitive, and runs significantly faster too.
I suspect your query would be expressed as:
As far as I understand such usage of GROUP BY and select columns is not standard in SQL world.
That is -- if we GROUP BY some fields, then in SELECT list we can have either fields we group by or other fields as arguments of aggregate functions.
We can write
But we cannot write
It is not clear -- what name out of all rows that have the same home_state do we want.
Consider following data in Sample.Person:
What name will following query return John or Jim?
I prefer following variant of the query with join:
That returns both rows from the sample data above.
Good point!
Regarding this
Some DBs don't respond well to X= {statement that returns more than one row } how would cache respond to two people that have are tied for being the youngest in a state
Conrad,
your question have two parts
a) For queries like
if subquery returns more than one row, Caché will compare left part with first value of subquery.
b) If several people are tied for the minimum age, then following query prints all of them