Question 21874 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search