Sorting column with LENGTH

MySQL database
#1

Im trying to sort a table in order to get the last record.

The following statement …
SELECT * FROM lessons WHERE class=‘DRW’ AND level=‘1’ ORDER BY LENGTH(lesson_code) ASC

produces the following result:

DRW1L1
DRW1L2
DRW1L3
DRW1L4
DRW1L5
DRW1L6
DRW1L7
DRW1L8
DRW1L9
DRW1L10
DRW1L11
DRW1L12

I am trying to get the LAST column in this sort…DRW1L12
When I sort in DESC order I get the following result

DRW1L10
DRW1L11
DRW1L12
DRW1L1
DRW1L2
DRW1L3
DRW1L4
DRW1L5
DRW1L6
DRW1L7
DRW1L8
DRW1L9

I want to use DESC LIMIT 1 in order to get DRW1L12

but instead im getting DRW1L10

Any advice would be appreciate!

#2

The sort is doing what you tell it. You need to pad your numbers so that they are the same length.

Like:

DRW1L009
DRW1L010
DRW1L011
etc

#3

Here you go…

SELECT * FROM lessons ORDER BY LENGTH(class) DESC, class DESC LIMIT 1 ;

#4

Hi there!

Im not sure why you put ‘class’ as the column to be ordered…but I modified what you gave me and it works now. Its important for me to specify ‘class’ and ‘level’ since those variables will differ for each search.

SELECT * FROM lessons WHERE class=‘DRW’ AND level=‘1’ ORDER BY LENGTH(lesson_code) DESC, lesson_code DESC LIMIT 1 ;

thanks!

#5

I went by the SQL query you provided. It only gives two specific column names of your database which is a good example of why you don’t select *. Other people have no idea what’s in the database. You didn’t specify the column name that the data example came from so I just picked one.

#6

At the start of my post I specified the columns I needed for the search. Anyway…thanks for helping me figure it out!

Sponsor our Newsletter | Privacy Policy | Terms of Service