I’m using SQL Trino trying to count the unique words (ignore any numbers) in a string column.

I am facing difficulties in a SQL query where I need to filter records based on a priority condition. If a record meets a priority condition (rn = 1), it should be returned, but if not, then checking for a secondary condition (rn2 = 1) should be done. What is the best way to structure this query?

I have a Query, in Trino Enginee, that I am creating several CTEs but I am losing the ORDER BY logic when I add the CTEs
Its like this:

I have a couple of tables where one shows, per record, and ID of a project with the metadata tag of a file. Clients upload multiple files per project ID. When the client uploads a file for a project ID, the file is marked with a tag. Some of those files are mandatory for all project ID’s. There is a second table where it stores all the mandatory tags.