Sorting both Alpanumeric and Numeric column in mysql

Question

Here is my table data:

 - file_number -   status
 - ABC099    -     running
 - ABC100    -     running
 - ABC101    -     removed
 - ...
 - ABC1001   -     removed
 - ABC1002   -     removed
 - ABC1003   -     running
 - BCA099    -     running
 - BCA100   -      removed
 - BCA101   -      running
 - ...
 - BCA1001   -     removed
 - BCA1002   -     running
 - BCA1003   -     running

This query returns the correct answer with file_number sorted:

SELECT file_number,status FROM table ORDER BY LENGTH(file_number) asc

But I want to sort the status column as well i.e all the running files should come first and removed files should be displayed at the end.

Expected result:

 - file_number  -  status
 - ABC099    -     running
 - ABC100    -     running
 - ABC1003   -     running
 - ...
 - BCA099    -     running
 - BCA101   -      running
 - ...
 - BCA1002   -     running
 - BCA1003   -     running
 - ABC101    -     removed
 - ABC1001   -     removed
 - ABC1002   -     removed
 - BCA100   -      removed 
 - BCA1001   -     removed

SELECT file_number,status FROM table ORDER BY LENGTH(file_number) asc, status desc. This too does not work — MEGHA POOJARY
– MEGHA POOJARY, Commented Mar 9, 2020 at 6:53

Tushar · Accepted Answer · 2020-03-09 09:04:27Z

1

You should try

SELECT file_number,status
FROM `table` 
ORDER BY status desc,
     LENGTH(file_number) asc,
     file_number asc

First you should order by status then you should order by length. Don't order by length first.

edited Mar 9, 2020 at 9:04

answered Mar 9, 2020 at 7:04

Tushar

5683 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MEGHA POOJARY Over a year ago

It does not sort by file_number but does sort by status

Barbaros Özhan · Accepted Answer · 2020-03-09 09:03:08Z

0

You can cast file_number as signed in order to make your order by the numeric part. And then add status desc part into ORDER BY clause to provide the desired alphabetical order if you have only two options for status column :

SELECT file_number,status
  FROM `table` 
 ORDER BY status DESC, CAST(file_number AS signed)

Demo

Update : If you also have different letter combinations for prefixing the file numbers and want to order prominently by them, then consider adding this column into ORDER BY list as the first component :

SELECT file_number,status
  FROM `table` 
 ORDER BY status DESC, file_number, CAST(file_number AS signed)

Demo 2

edited Mar 9, 2020 at 9:03

answered Mar 9, 2020 at 7:18

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

6 Comments

MEGHA POOJARY Over a year ago

Tried but does not sort by file_number

Barbaros Özhan Over a year ago

@MEGHAPOOJARY have you looked at the demo? which yields the results exactly as you wish.

MEGHA POOJARY Over a year ago

my table has file names as ABC001,ABC002 etc as well as BCA001,BCA002 etc and CAB001,CAB002 etc

Barbaros Özhan Over a year ago

please exactly state the sample data and desired results by editing your question @MEGHAPOOJARY

MEGHA POOJARY Over a year ago

Edited the question.. In your demo two the file_number is sorted not the status.

|

Collectives™ on Stack Overflow

Sorting both Alpanumeric and Numeric column in mysql

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related