CODEWITHSUNDEEP
sql-order-bysphinxsphinxql
koseduhemak
koseduhemak

Reputation: 551

Sphinx - native (human) sort of strings

I am using Sphinx Search (http://sphinxsearch.com/) in combination with SphinxQL and need to sort by a string attribute:

I configured the attribute as sql_attr_string = myattribute.

Example query: SELECT * FROM myindex ORDER BY myattribute ASC;

The order I get with order by myattribute ASC: 1a, 100b, 101c, 2a, 3a

The order I want: 1a, 2a, 3a, 100b, 101c

Is there a way to achieve this?

Upvotes: 1

Views: 414

Answers (1)

Manticore Search
Manticore Search

Reputation: 1482

What you can do is add another attribute containing length of the string attribute and then sort first by the length ASC, then by the string ASC. This fixes the order in the example you've provided in the question: mysql> select * from idx_min order by l asc, myattribute asc; +------+-------------+------+ | id | myattribute | l | +------+-------------+------+ | 1 | 1a | 2 | | 4 | 2a | 2 | | 5 | 3a | 2 | | 2 | 100b | 3 | | 3 | 101c | 3 | | 6 | a200 | 3 | | 7 | c345 | 3 | | 8 | a345-2 | 5 | +------+-------------+------+ 8 rows in set (0.00 sec)

However you may want to have a200, c345, a345-2 (from your comment) before 1a. Then I guess the only way you can do it in Sphinx / Manticoresearch is by using UDFs (user defined functions). You need to create a function which will convert your string into a number given that a-z should have higher values than 0-9. Then you can use the result of the function in your query. Like this:

select *, natsort(myattribute) ns from idx_min order by ns asc;

Upvotes: 1

Related Questions