When the data having same date( even the micro second ) like below table. If I There is a table below

I would like to get the latest record per user ID.

When I try to sort the Create_At column by descending order as follow,

SELECT * FROM Balance_Table ORDER BY Create_AT Desc;

What is the logic behind this sorting?

CodePudding user response：

Your current query is just sorting the entire table on the created at time. You could use the following LIMIT query to find the single most recent record:

SELECT * FROM Balance_Table ORDER BY Create_AT DESC LIMIT 1;

But, this would just return a single user's records. Instead, what you want to be using here on MySQL 8 is ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY Create_AT DESC) rn
    FROM Balance_Table
)

SELECT Create_At, User_ID, Balance
FROM cte
WHERE rn = 1;

CodePudding user response：

SELECT * FROM table ORDER BY score DESC Above query ordering the data only by the score. If you want to order the data by another field as you mentioned, you have the add that field also to the query as below. You have mentions that you want to order by name in acceding order (C > D >E). So I used ORDER BY ASC. Below query will give you the output that you requested.

SELECT * FROM table ORDER BY score DESC, name ASC