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

How to sort on descending order form a column which has both int and float values in mysql?

I have a column called chapterNumber with the datatype varchar which stores both int and float values.

for only Int value if I do order by chapterNumber desc it works fine but when there are both float and int values in there order by chapterNumber desc doesn’t work and results in

Chapter 6.5

Chapter 6

Chapter 52.5

Chapter 52

Chapter 5.2

Chapter 5

Chapter 4.5

Chapter 4

I want the numbers to appear in an descending order

Tags:

2

Answers


  1. 0

    Extract the number at the end of the chapter string, and convert it to a numeric datatype so they can be compared.

    ORDER BY CAST(SUBSTRING_INDEX(chapterNumber, '', -1) AS DECIMAL) DESC
    Login or Signup to reply.
  2. 0

    To sort a column in descending order that contains both integer and float values stored as varchar in MySQL, you can convert the varchar values to numbers before sorting.
    You can use the CAST() or CONVERT() function to achieve this.
    Here’s an example query to sort the chapterNumber column in descending order:

    SELECT * FROM your_table ORDER BY CAST(chapterNumber AS DECIMAL(10, 2)) DESC; In this query: CAST(chapterNumber AS DECIMAL(10, 2))

    converts the varchar values in the chapterNumber column to a decimal (floating-point) number with a precision of 10 and a scale of 2.
    You can adjust the precision and scale to fit your data. DESC specifies that you want to sort the result in descending order.
    By using this approach, you will be able to sort both integer and floating-point values in descending order correctly.
    The CAST() function will ensure that the values are treated as numeric values for sorting.

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

Back To Top
Search