What is the process for sorting non-integer records in MySql?

Question

What is the process for sorting non-integer records in MySql?

I need help sorting MySQL records in descending order to retrieve the most recent entry.

Here are the records stored as strings:

I specifically want to retrieve the value 1/2018. The entries correspond to each year and the latest one added was 1/2018, followed by 2/2018, and so forth.

This is the query I am using:

SELECT NoControl FROM cita ORDER BY NoControl DESC LIMIT 1

Unfortunately, instead of 1/2018, I am currently getting 3/2017 as the last record returned.

php html mysql phpmyadmin

Answer 1

Answer №1

If you come across the pattern m/yyyy, you can manipulate strings using a function like this:

SELECT NoControl 
FROM cita 
ORDER BY right(NoControl,4) DESC, left(NoControl,1) DESC LIMIT 1

It's best to avoid storing date values as strings...

Instead, consider converting the string into a proper date format like this:

SELECT NoControl 
FROM cita 
ORDER BY str_to_date(NoControl,'%m/%Y') DESC LIMIT 1

Answer 2

If you come across the pattern m/yyyy, you can manipulate strings using a function like this:

SELECT NoControl 
FROM cita 
ORDER BY right(NoControl,4) DESC, left(NoControl,1) DESC LIMIT 1

It's best to avoid storing date values as strings...

Instead, consider converting the string into a proper date format like this:

SELECT NoControl 
FROM cita 
ORDER BY str_to_date(NoControl,'%m/%Y') DESC LIMIT 1

Answer 3

Answer №2

Utilize the SUBSTRING_INDEX function for extracting the first and second parts, then convert them to integers and sort:

SELECT str
FROM testdata
ORDER BY 
    CAST(SUBSTRING_INDEX(str, '/', -1) AS UNSIGNED) DESC,
    CAST(SUBSTRING_INDEX(str, '/',  1) AS UNSIGNED) DESC
LIMIT 0, 1

SQL Fiddle

Answer 4

Utilize the SUBSTRING_INDEX function for extracting the first and second parts, then convert them to integers and sort:

SELECT str
FROM testdata
ORDER BY 
    CAST(SUBSTRING_INDEX(str, '/', -1) AS UNSIGNED) DESC,
    CAST(SUBSTRING_INDEX(str, '/',  1) AS UNSIGNED) DESC
LIMIT 0, 1

SQL Fiddle

What is the process for sorting non-integer records in MySql?

Answer №1

Answer №2

Similar questions

Unable to retrieve file name with Jquery Ajax and PHP due to error

Place an image on top of adsense

Update the content of a div element with the data retrieved through an Ajax response

Tips on avoiding quotation marks in a Less variable containing a color identifier

What are the best methods for ensuring a website maintains consistent visibility across all different screen sizes?

Issue with overlay functionality after updating to jQuery version 1.12.1

What is the best way to make a CSS element move with Javascript?

JavaScript Summation Calculation

Troubleshooting: Android compatibility issues with dynamic source for HTML 5 video

The display of website content across various screens

What is the best way to customize the appearance of only one of two identical tables using HTML and CSS?

Unable to change the DateTime back to the current time

Position a div container to be aligned at the bottom of an image

Adjust prices of multiple specific products selectively in Woocommerce

How come flex won't let me rearrange an image?

What are the steps to integrating databases with HTML5?

Troubles with aligning and floating three divs in the center of a container div – a CSS conundrum illustrated with code snippets and a basic diagram

What is the best way to direct visitors who are not logged in to the Login page in Wordpress?

What is the method for modifying the chosen color using a select option tag instead of a list?

Using ReactJS to strip HTML tags from JSON response