Sorting dates in MySQL table (not sorting BY date)

Question

How can I sort the dates in a column in a table? (timestamp)

Please note that I'm not asking to sort rows by date, but rather I'm asking how to swap dates in records so that all of the dates are in the correct order; without changing anything else.

So; for example:

id     foo      bar
1      A        03-03-2030
2      B        01-01-2010
3      C        02-02-2020

becomes...

id     foo      bar
1      A        01-01-2010
2      B        02-02-2020
3      C        03-03-2030

Preferred solution uses straight MySQL or MySQL and PHP.

What version of MySQL? And is that date column really a date or is it varchar (it's not a valid MySQL date format...) — Nick
– Nick, Commented Feb 3, 2020 at 5:30
MySQL 8.x and it's a timestamp (as I stated in the question) — HardlyNoticeable
– HardlyNoticeable, Commented Feb 3, 2020 at 5:33
I'd suggest putting the bar column into a new table, sorting it, and putting it back in place. Depending on how many entries you have, you could query the table, sort the results in a PHP array, and then query the table again. — Jeff Vdovjak
– Jeff Vdovjak, Commented Feb 3, 2020 at 5:39

Nick · Accepted Answer · 2020-02-03 05:40:12Z

2

You can update your table with a MySQL query using a CTE which computes the ROW_NUMBER() for each row, sorted by id and bar; replacing the bar value for each id with the one which has the corresponding sorted by bar row number:

WITH CTE AS (
  SELECT id, bar,
         ROW_NUMBER() OVER (ORDER BY id) AS rn_id,
         ROW_NUMBER() OVER (ORDER BY bar) AS rn_bar
  FROM data
)
UPDATE data d
JOIN CTE c1 ON c1.id = d.id
JOIN CTE c2 ON c2.rn_bar = c1.rn_id
SET d.bar = c2.bar

Output (for your sample data):

id  foo     bar
1   A       2010-01-01
2   B       2020-02-02
3   C       2030-03-03

Demo on dbfiddle

answered Feb 3, 2020 at 5:40

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

HardlyNoticeable Over a year ago

Worked perfectly. And fast! I hadn't used common table expressions before. But now I love them.

Jeff Vdovjak Over a year ago

I just finished solving it with PHP :) But this is so much better!

Nick Over a year ago

@HardlyNoticeable yeah, CTEs and window functions are the best things about MySQL8. I'm still stuck on 5.7 on a big project and really missing them...

Nick Over a year ago

@JeffVdovjak sorry about that! If it was MySQL 5.7 or below I think PHP would probably be the easier way to do it.

Jeff Vdovjak Over a year ago

@Nick That's okay. Besides the practice of working through a problem, I got to learn something new. I'm very happy with your solution and will explore CTEs soon. A new tool for the toolbox.

Collectives™ on Stack Overflow

Sorting dates in MySQL table (not sorting BY date)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related