MySQL – How to Split a String and ORDER BY DESC Matching Values

MySQL

From a Query like:

SELECT id 
FROM table 
ORDER BY
        column = 'A B C D E F G' DESC,
        column = 'A B C D E F' DESC,
        column = 'A B C D E' DESC,
        column = 'A B C D' DESC,
        column = 'A B C' DESC,
        column = 'A B' DESC,
        column = 'A' DESC
GROUP BY id

And the Answer I got was to use

SELECT id 
FROM table 
ORDER BY
        SUBSTRING_INDEX(column, ' ', 1),
        LENGTH(column) DESC
GROUP BY id

The problem that it seems that I can't define a specific string to it And it orders the full-records only, What I want is something to order the matches of the ORDER BY at top by its highest-matching value as the ladder of defined string.

In all cases, Is there any performance difference between my first Query of ladder column = . . . . DESC,. . . And any other solution? And what is the other solution for such a Query to select element in a specific string parts order.

The table is

[id  -  column       ]
[1   -  A B C D E    ]
[2   -  A B C        ]
[3   -  A B C D E F G]
[4   -  X Y Z        ]
[5   -  A            ]
[6   -  B            ]
[7   -  A B C        ]
[8   -  B A C D      ]
[9   -  A B G C D E F]
[10  -  A B C D E F G H I J K]
[11  -  Z A B C D E]

And I have this string A B C D E F G that I want to get all the rows matching its parts, So I want to order like ORDER BY PARTSOF('A B C D E F G') DESC

to get the result of

[id - column]
[3  - A B C D E F G]
[1  - A B C D E]
[2  - A B C]
[7  - A B C]
[5  - A]
. . . EVERYTHING AFTER IS BY ID . . .
[4  - X Y Z        ]
[6  - B            ]
[8  - B A C D      ]
[9  - A B G C D E F]
[10 - A B C D E F G H I J K]
[11 - Z A B C D E  ]

Juse like this SQLFiddle http://sqlfiddle.com/#!9/9f4c815/2/0

Best Answer

It seems like you want in the result first the substrings of a parameter string ('A B C D E F G'). In this case the LIKE should be reversed - and it only needs the wild character in the end, not on both sides:

 'A B C D E F G' LIKE CONCAT(col, '%')

If this goes to the WHERE clause, it will affect only what results you get of course, not the order. If you want to affect the ordering, (first the rows that match the above, then the rest), then it should be in the ORDER BY:

SELECT id, col
FROM t 
ORDER BY 
 -- CASE WHEN 'A B C D E F G' LIKE CONCAT(col, '%') THEN 0 ELSE 1 END,
    CASE WHEN 'A B C D E F G' LIKE CONCAT(col, '%') THEN col END DESC,
    id ;

Since MySQL orders nulls first when ascending and last when descending, we can remove the first CASE expression from the ORDER BY (commented above).

Test in fiddle.

You may also want to try this variation that separates the two parts and then UNIONs them. It has an additional (redundant) clause in the first WHERE, to make the index use more efficient for the first part. The second part will have to do a table or index scan anyway:

( SELECT id, col
  FROM t 
  WHERE col >= LEFT('A B C D E F G', 1) 
    AND col <= 'A B C D E F G'
    AND 'A B C D E F G' LIKE CONCAT(col, '%')
  ORDER BY col DESC
)
UNION ALL
( SELECT id, col
  FROM t 
  WHERE NOT ('A B C D E F G' LIKE CONCAT(col, '%'))
  ORDER BY id
) ;

Related Solutions

MySQL – Using Index for Both ASC and DESC on String Column

Although the syntax for CREATE INDEX supports an option for defining the index as ascending or descending, in the most common two storage engines (InnoDB and MyISAM), this index option is a no-op. There is no such thing as an "ascending" or "descending" index, they are just indexes and can be used for sorting in either direction.

But in the case you describe, where you have a multi-column index and you want to order by one column ascending and the other column descending, that's a different story. The sort order must go in the same direction for both columns if it uses an index.

(Not that every sort has to use an index. You can certainly run the query and let it sort matching rows with a filesort.)

To reverse the sort order of strings, you could add your own custom collation. https://dev.mysql.com/doc/refman/5.6/en/adding-collation.html

But you'd have to store your strings using that new collation, and rebuild your index. The index would only be useful for the reverse-sorting.

Re comment from @Cratylus:

A collation is what defines "A" as being before "B" and all the other ordering of letters. So if you define a collation that says that "B" is before "A", and use that collation in your column definition, then sorting on that column, or creating an index on the column, would be in the reverse order.

Note: the following doesn't create a proper collation, it just demonstrates the steps to create one.

Edit /usr/share/mysql/charsets/Index.xml and make this edit:

<charset name="latin1">

  . . . leave other collations alone . . .

  <collation name="latin1_reverse_ci" id="251">
    <order>Dutch</order>
    <order>English</order>
    <order>French</order>
    <order>German Duden</order>
    <order>Italian</order>
    <order>Latin</order>
    <order>Portuguese</order>
    <order>Spanish</order>
  </collation>

</charset>

Edit /usr/share/mysql/charsets/latin1.xml and make this edit:

<charset name="latin1">

. . . leave other collations alone . . .

<collation name="latin1_reverse_ci">
<map>
 AE B1 AC A2 A0 9E 9C 8D BE 8B 89 87 85 83 7F 59
 73 71 6F 6D 63 61 5F 5D 55 4F 4D 4B 49 47 45 43
 97 B1 AC A2 A0 9E 9C 8D BD 8B 89 87 85 83 7F 59
 73 71 6F 6D 63 61 5F 5D 55 4F 4D 4B 49 47 45 43
 FF FE FD FC FB FA F9 F8 F7 F6 F5 F4 F3 F2 F1 F0
 EF EE ED EC EB EA E9 E8 E7 E6 E5 E4 E3 E2 E1 E0
 DF DE DD DC DB DA D9 D8 D7 D6 D5 D4 D3 D2 D1 D0
 CF CE CD CC CB CA C9 C8 C7 C6 C5 C4 C3 C2 C1 C0
 BF BC BB BA B9 AF AA A8 A6 A4 9A 98 95 93 91 8F
 81 7D 7B 79 77 75 6B 69 67 65 5B 57 53 51 41 B8
 B7 B6 B5 B4 B3 AF AA A8 A6 A4 9A 98 95 93 91 8F
 81 7D 7B 79 77 75 6B 69 67 65 5B 57 53 51 41 40
 3F 3E 3D 3C 3B 3A 39 38 37 36 35 34 33 32 31 30
 2F 2E 2D 2C 2B 2A 29 28 27 26 25 24 23 22 21 20
 1F 1E 1D 1C 1B 1A 19 18 17 16 15 14 13 12 11 10
 0F 0E 0D 0C 0B 0A 09 08 07 06 05 04 03 02 01 00
</map>
</collation>

</charset>
. . .

Restart mysqld.

Confirm you have the new collation:

mysql> SHOW COLLATION LIKE 'latin1%';
+---------------------+---------+-----+---------+----------+---------+
| Collation           | Charset | Id  | Default | Compiled | Sortlen |
+---------------------+---------+-----+---------+----------+---------+
. . .
| latin1_general_ci   | latin1  |  48 |         | Yes      |       1 |
| latin1_general_cs   | latin1  |  49 |         | Yes      |       1 |
| latin1_spanish_ci   | latin1  |  94 |         | Yes      |       1 |
| latin1_reverse_ci   | latin1  | 251 |         |          |       0 |
. . .

Create a table and fill it with some text:

mysql> CREATE TABLE foo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    t VARCHAR(60) COLLATE latin1_reverse_ci, 
    x DATE,
    KEY(t)
);

mysql> INSERT INTO foo (t) VALUES ('Harry'), ('Ron'), ('Hermione');

Demonstrate that the sort order is the reverse of what you'd think it should be:

mysql> SELECT * FROM foo ORDER BY t ASC;
+----+----------+------+
| id | t        | x    |
+----+----------+------+
|  2 | Ron      | NULL |
|  3 | Hermione | NULL |
|  1 | Harry    | NULL |
+----+----------+------+

Confirm that it doesn't use a filesort:

mysql> EXPLAIN SELECT * FROM foo WHERE t LIKE 'H%' ORDER BY t\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
   partitions: NULL
         type: range
possible_keys: t
          key: t
      key_len: 63
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

I had to put a condition on the rows, because otherwise it thought it was going to read all the rows in the table, and it did a table-scan and filesort.

Re comment from @ypercube:

It doesn't reverse the order of characters in a given string like REVERSE(), it reverses which characters are considered greater than or less than each other when comparing two strings. So no, the CHAR padding spaces don't matter, they're still at the end of both strings.

Example (no index this time, just using order by):

mysql> create table t (c char(20) collate latin1_reverse_ci);
mysql> insert into t values ('A'), ('B'), ('C');
mysql> select * from t order by c;
+------+
| c    |
+------+
| C    |
| B    |
| A    |
+------+

Edit: no, @ypercube is correct, this doesn't work with CHAR because the space character is in the wrong place when I naively reorder the collation simply by putting all the bytes in the reverse order. That's not the proper way to design a collation.

So this answer should serve as a demo of the mechanics of creating a collation, not as a proper collation for reversing alphabetical order.

Mysql Order , Select highest value item and update to another table

In this case you can use INSERT INTO ... SELECT FROM ... ORDER BY DESC LIMIT 1:

INSERT INTO data_2 (name, text1, text2)
SELECT name, serial, CAST(point AS varchar(10))
FROM data_1
ORDER BY point DESC
LIMIT 1

Best Answer

Related Solutions

MySQL – Using Index for Both ASC and DESC on String Column

Mysql Order , Select highest value item and update to another table

Related Question