How ORDER BY works in my sql when the data is same?

Question

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

Create_At	User_ID	Balance
2022-09-29 09h:09:01.761335	4	200300
2022-09-30 12h:09:47.405520	6	58111
2022-09-30 12h:09:47.405520	6	53861
2022-09-29 11h:09:46.276274	6	79011

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?

Answer 1

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;

Given that some users have more than one record as tied for the latest, it might make more sense to use RANK(), instead of ROW_NUMBER(), and report all ties.