Mysql语句获取最新记录求助？

Question

Mysql查询最新记录的语句
P1 500 10-01-2023
P1 500 10-02-2023
P1 1000 10-05-2023
P2 700 10-03-2023
P2 1500 10-05-2023
P2 1000 01-07-2023
P3 1500 10-05-2023
P3 5000 01-06-2023

Sql Results:
P1 1000 10-05-2023
P2 1000 01-07-2023
P3 5000 01-06-2023

P1,P2,P3 为用户名，数字为贷款金额，最后为日期
要求:查询所有用户最近一次的贷款记录，最终得到的结果如Sal Results所示，表名和列名可自定义，只需提供查询语句. 能否用最简单最明了的方式解决

能否用最简单最明了的方式解决?
我一开始是写 Select *from bank order by date desc;
不知道这里哪里错

Answer 1

WITH RankedData AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY xxcol_name ORDER BY xxcol_date DESC) AS rnk
    FROM
        xx
)
SELECT
    idxx,
    xxcol_name,
    xxcol_val,
    xxcol_date
FROM
    RankedData
WHERE
    rnk = 1;

Answer 2

SELECT b.*
FROM bank b
INNER JOIN (
    SELECT username, MAX(date) as max_date
    FROM bank
    GROUP BY username
) as latest
ON b.username = latest.username AND b.date = latest.max_date;