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

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

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0 
    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

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

Back To Top
Search