Splitting a String and ORDER BY DESC its matching Values 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
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 &gt;= LEFT('A B C D E F G', 1) 
    AND col &lt;= '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
) ;