SQL Order By, But Invert For One Item
I have a SQL query that matches the following
Code int1 int2 int3 S
C12 21 22 14 1
C33 43 56 2 3
C34 23 2 1 3
C55 33 92 12 5
CB56 45 66 10 5
MA10 10 11 12 1
This is the result of using OrderBy on Code However I don't want it to order according to the alphabets But according to the number after it for ex 1 in M1 and 33 in C33 In some cases the number after the alphabet may be 3 digits like E344 What I want, to make it look like
Code int1 int2 int3 S
MA10 10 11 12 1
C12 21 22 14 1
C323 43 56 2 3
C325 43 56 2 3
C34 23 2 1 3
C525 33 92 12 5
CB56 45 66 10 5
I need
M Should always show on top if present Then Sort by number in first place Then Sort by number in second place Then sort it by number in third place
The 'field will always consist of the first digit from the code
source to share
step 1 splits the column into 2 columns, 1 contains letters and the other contains numbers:
SELECT
substring(code,0,PATINDEX('%[0-9]%', Code)) as letters,
substring(code,PATINDEX('%[0-9]%', Code)) as numbers,
fields
FROM table
step 2 Convert numbers to integer and sort
CONVERT(substring(code,PATINDEX('%[0-9]%', Code)),UNSIGNED INTEGER) as numbers
Step 3 Sort
Order by field1 asc, field2 desc... etc
In a subquery it might be easier:
select * from
(SELECT
substring(code,0,PATINDEX('%[0-9]%', Code)) as letters,
CONVERT(substring(code,PATINDEX('%[0-9]%', Code)),UNSIGNED INTEGER) as numbers,
fields
FROM table) T
order by numbers asc, letters desc
source to share
It's conceptually simple: you want to order first, whether the code starts with the letter "M" or not, and then the numeric part of the code. You say in the comment:
extracting the first digit then ordering and then extracting the second and then ordering and then extracting the third digit and then ordering
This is how alphabetical order has always worked. You order the first character; when it's the same as the one you order with the second character, etc., so you don't need special treatment in this case. Just enter the numeric part of the code as a string, then order it.
At this stage, it remains only to decide how to extract the digital part of the code. This would be easy with PATINDEX()
(as Alphonse pointed out), but unfortunately MySQL does not support PATINDEX()
as far as I know.
Now, what follows is extremely ugly, but it does work. Basically we get the non-numeric part of a string by removing all numeric characters from it, and then use the length of the non-numeric part to extract the numeric part.
SELECT mytable.* FROM mytable
INNER JOIN (
SELECT
code,
RIGHT(code, LENGTH(letters)) numbers
FROM (
SELECT
code,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
code, 0, ''), 1, ''), 2, ''), 3, ''), 4, '')
, 5, ''), 6, ''), 7, ''), 8, ''), 9, '') letters
FROM mytable
) letters_table
) numbers_table
ON numbers_table.code = mytable.code
ORDER BY (numbers_table.code like 'M%') DESC,
numbers_table.numbers ASC
This solution is probably ineffective. However, I don't think you can get any acceptable performance anyway, unless you store the numeric portion in a separate column that you can index.
source to share
Since you are guaranteeing that s is the value of the first digit in the code, this can be used to find the start of the code and from there, get the numeric portion of the code. Since you want all codes starting with "M" first, this results in the following sentence ORDER BY
:
... ORDER BY IF(SUBSTR(code, 1, 1) = 'M', 0, 1), SUBSTR(code, LOCATE(s, code))
source to share