CODEWITHSUNDEEP
phpmysqlsql-order-by
Suneth Kalhara
Suneth Kalhara

Reputation: 1221

MySQL PHP order by

I'm writing MySQL query for sort this kind of data

Traffic
100/40
12/1
50/20
25/5

the value get by devide right hand side number from left hand side number.

also i have multi columns for sort

ex - SELECT * FROM fltable ORDER BY Traffic DESC,Speed ASC,Cost ASC

I need to sort those data ascending order and descending order, can anyone help me with this.

Thank you

Upvotes: 0

Views: 118

Answers (2)

Strawberry
Strawberry

Reputation: 33935

SELECT * FROM fractions;
+-----------+
| fractions |
+-----------+
| 100/40    |
| 12/1      |
| 25/5      |
| 50/20     |
+-----------+

SELECT fractions
     , SUBSTRING_INDEX(fractions,'/',1)/SUBSTRING_INDEX(fractions,'/',-1)x 
  FROM fractions 
 ORDER 
    BY x DESC;
+-----------+------+
| fractions | x    |
+-----------+------+
| 12/1      |   12 |
| 25/5      |    5 |
| 100/40    |  2.5 |
| 50/20     |  2.5 |
+-----------+------+

Upvotes: 2

Bora
Bora

Reputation: 10717

Try something like following query:

SELECT *, CONVERT(SUBSTR(var, 1, POSITION('/' IN var) - 1), UNSIGNED INTEGER) as num 
FROM table 
ORDER BY num DESC

Get number before / with SUBSTR and CONVERT to int

Result with num column

100
50
25
12

Upvotes: 1

Related Questions