Return all the results for the latest date

I have a table with the following columns:

• city_id = city ID
• city_name = city name
• shape = polygon -> geography field
• date = date when the shape was created

and populated with the following sample data:

I've crafted the following query:

SELECT city_id, 
       city_name, 
       shape,
       date
FROM (SELECT city_id, 
             city_name, 
             ANY_VALUE(shape) AS shape, 
             date, 
             ROW_NUMBER() OVER (PARTITION BY city_id, city_name ORDER BY date DESC) AS rank
      FROM test
      GROUP BY 1,2,4,5)
WHERE rank = 1
ORDER BY date DESC

Current result:

With this query, I was hoping that using the PARTITION function and rank = 1 would return all the records for a city where several shapes have been created the same day, for the latest date. The query works fine for cities with only 1 shape created on the latest date.

Expected output:

Any idea on how to return all the shapes created on the latest date for each city?

Thank you in advance!

The DENSE_RANK window function is better used here. It's cousin ROW_NUMBER allows unique ranking values only, regardless of ties.

SELECT city_id, city_name, shape, date
FROM (SELECT city_id, city_name, shape, date, 
             DENSE_RANK() OVER (PARTITION BY city_id ORDER BY date DESC) AS rn
      FROM test) cte
WHERE rn = 1
ORDER BY date DESC

Also you can remove:

• the whole GROUP BY clause from the subquery

• "city_name" from the PARTITION BY clause, assuming that your "city_id" values uniquely identify cities.

If you want the records of the last date for each city thin try this.

SELECT test.city_id, 
       test.city_name, 
       ANY_VALUE(shape) AS shape, 
       test.date
FROM test inner join (SELECT city_id, 
             city_name, 
             MAX(date) AS MAX_DATE
      FROM test
	  GROUP BY city_id, city_name) m ON test.city_id = m.city_id AND test.date = m.MAX_DATE
ORDER BY date DESC