I have the following calculated field:

CONCAT(idC, '/', DATE_FORMAT(entryDate, '%Y')) as idC

I have idC stored on database, that's a ''fake'' id that increments based on another field called entryDate, so it's not a unique number.

On the get query I concat that id with the entry date so it returns for example 1/2019, 2/2019, 1/2020...

The problem is that I want that id to be ordered ascending like a 'date' so it should be 1/2019, 2/2019, 1/2020

The order that MySQL returns is 1/2019, 1/2020, 2/2019 because as a string it makes sense to give that answer

Is there any way to make MySQL return the desired order by?

  • ORDER BY SUBSTRING_INDEX(idC, '/', -1), SUBSTRING_INDEX(idC, '/', 1) – Akina Jul 12 at 12:18
  • the returns is the same because it is ordering first by the first part of the string, and because I have duplicate idC it'll return first 1/2020 than 1/2019 – JoseCarlosPB Jul 12 at 12:24
  • Before you claim, it was necessary to check. fiddle. – Akina Jul 12 at 12:29
  • This is more like the question I asked, I don't know why it doesn't work the field is calculated dbfiddle.uk/… – JoseCarlosPB Jul 12 at 12:37
  • 1
    Could you do: order by DATE_FORMAT(entryDate, '%Y') asc, idC asc; – raphael75 Jul 12 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.