Is it possible to to retrieve a top ROW_NUMBER() for each group without a sub-query?

Question

In the spirit of succinct and beautiful coding, I am wondering if anyone has been able to create a query that performs exactly as below, but with no sub-queries.

Example query:

Essentially, the sub-query is retrieving the most recent date for each user by first ordering and labeling each row for each patient where "1" is the most recent date for each patient. The outer query then identifies and returns each "1" which is the max date associated with each user.

SELECT user_name
      ,date
FROM(
    SELECT user_name
          ,date
          ,row_number() OVER(PARTITION BY user_name ORDER BY date DESC) as row_num
    FROM yourtable
) AS t
WHERE t.row_num = 1

Data example pre-execution:

|user_name|date |
|Some1    |10/22|
|Some1    |10/27|
|Some1    |10/30|
|Some2    |10/30|
|Some2    |11/15|
|Some2    |11/18|

Data example post-execution:

|user_name|date |
|Some1    |10/30|
|Some2    |11/18|

Preparing to have my mind blown by you all

Answer 1

For your example, you can use group by:

select user_name, max(date)
from t
group by user_name;