CODEWITHSUNDEEP
mysqlsql-order-by
traildex
traildex

Reputation: 358

Mysql, how to order by the first number in a set of numbers

I need to order by a field that contains a set of numbers. Lets say a table named TEST contains ID, NAME, QUADS with QUADS as follows.

95,273,212,405
717,450,771,504
391,176,646,272

This are the results I am getting with a query such as

SELECT * FROM TEST ORDER BY QUADS
391,176,646,272
717,450,771,504
95,273,212,405

These are the results I am looking to get

95,273,212,405
391,176,646,272
717,450,771,504

I am only interested in the first number in the set for "order". Figure it might be possible with a substring to the comma but not sure how to do that in MySQL.

Upvotes: 2

Views: 218

Answers (3)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this:

select * from test
order by cast(substring_index(quads,',',1) as unsigned)

Upvotes: 3

Aleksandar Vucetic
Aleksandar Vucetic

Reputation: 14953

Try with this:

select QUADS, 0+QUADS as S from TEST order by S

0+QUADS will convert your string to int and will use for it just the first digits sequence before "," which is actually what you want.

Upvotes: 2

Jim Garrison
Jim Garrison

Reputation: 86774

What you want is the substring_index function.

... order by substring_index(x_field,',',1)

This extracts the text in x_field up to the first occurrence of the comma delimiter

Upvotes: 2

Related Questions