Sorting values in ascending order based on number and year in MySQL. The oldest year and smallest number value means the record shows up first in asc

Question

I'm trying to sort a MySQL table data based on a column values.

id	record_number	created_at	updated_at
1	ent/4/2022	2022-10-24 20:34:25	2022-10-24 20:34:25
3	ent/6/2021	2022-10-24 20:35:03	2022-10-24 20:35:03

The column is named record_number and the values of column follow the format ent/4/2022, where ent is common in all entries and 4 is the record number and 2022 is the year the record was created.

How can I sort the records in a MySQL query such that an entry like ent/6/2021 shows up before ent/4/2022 when displaying the sorting results in ascending order?

The sorting result in descending order should look something like this.

id	record_number	created_at	updated_at
1	ent/4/2022	2022-10-24 20:34:25	2022-10-24 20:34:25
3	ent/6/2021	2022-10-24 20:35:03	2022-10-24 20:35:03

This implies the record id = 1 is newer compared to record with id = 3 because it was created in 2021 and the latter in 2022.

Please [edit] you question to include (as text) sample data and the desired result. Also indicate data types of your columns. — PM 77-1, Oct 24 '22 at 19:28
@PM77-1 edited to add sample data. The `record_number` column is of ` text` data type. — Eric kioko, Oct 24 '22 at 20:25
Can you please add an additional row for each year? Need to understand the sorting within the same record year. — PM 77-1, Oct 24 '22 at 20:31

kmoser · Accepted Answer · 2022-10-24T21:10:48.593

ORDER BY
  1*RIGHT(record_number, 4), /* extracts the year (assumes a 4-digit year) */
  1*SUBSTR( SUBSTRING_INDEX(record_number, '/', 2), 5 ) /* extracts record number */

1* converts the extracted strings into INT so they can be sorted numerically rather than alphabetically.

If years won't always be 4 digits, use:

ORDER BY
  1*RIGHT(record_number, LOCATE('/', REVERSE(record_number))-1),
  1*SUBSTR( SUBSTRING_INDEX(record_number, '/', 2), 5 )

MYSQL : Find the last occurrence of a character in a string

Ergest Basha · Answer 2 · 2022-10-24T20:54:48.403

One way:

select * 
from test
order by SUBSTRING_INDEX(SUBSTRING_INDEX(record_number,'/',-2),'/',+1) *1  asc,
         str_to_date(replace(record_number,'ent','00'),'%d/%m/%Y') desc;

https://dbfiddle.uk/nUOCNgy_

First you need to disable NO_ZERO_DATE then replace ent with 00 and use str_to_date to form a date column .

The line SUBSTRING_INDEX(SUBSTRING_INDEX(record_number,'/',-2),'/',+1) *1 asc will get only the charchters between / / and cast it to number

Sorting values in ascending order based on number and year in MySQL. The oldest year and smallest number value means the record shows up first in asc

2 Answers2