Why does microsoft access sql not run "top 1 with ties?"-CodePudding

using top with ties with row_number()

select top 1 with ties
  project_id, update_date, update_text
from projects
order by row_number() over (partition by project_id order by update_date desc)

rextester demo: http://rextester.com/MGUNU86353

returns:

 ------------ ------------- ---------------------------- 
| project_id | update_date |        update_text         |
 ------------ ------------- ---------------------------- 
|          1 | 2017-01-01  | Happy new year.            |
|          2 | 2017-02-14  | Happy Valentine's          |
|          3 | 2017-01-01  | A New year is a good thing |
 ------------ ------------- ----------------------------

Microsoft access does not like "with" in the "with ties." Why?

CodePudding user response：

Microsoft Access supports a set of SQL statements not as rich as mainstream relational database management systems, such as SQL Server, Oracle, Teradata, etc. The language supported is limited to that documented here:https://support.microsoft.com/en-us/office/access-sql-basic-concepts-vocabulary-and-syntax-444d0303-cde1-424e-9a74-e8dc3e460671

It certainly does not support TOP N WITH... construct, or the windowing / analytics functions used in your question.