MySQL PHP order by

Question

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

Are these values stored as VARCHAR? Or you want to order them by the result of the dividing? — Mahmoud Gamal
– Mahmoud Gamal, Commented Sep 4, 2013 at 11:39
ascending order or descending order,yes those are in varchar format — Suneth Kalhara
– Suneth Kalhara, Commented Sep 4, 2013 at 11:40
How do you want to sort them? By the first number before the / or by the second number after the / or by the result of dividing them? Please edit your question and add these details to it. — Mahmoud Gamal
– Mahmoud Gamal, Commented Sep 4, 2013 at 11:42

Strawberry · Accepted Answer · 2013-09-04 12:14:55Z

2

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 |
+-----------+------+

answered Sep 4, 2013 at 12:14

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Suneth Kalhara Over a year ago

thanks for the answer but i have multi columns for sort ex - SELECT * FROM fltable ORDER BY Traffic DESC,Speed ASC,Cost ASC , do you know how to use this with above query(traffic=fractions), thanks again.

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 4, 2013 at 11:47

Bora

10.7k5 gold badges50 silver badges76 bronze badges

1 Comment

Suneth Kalhara Over a year ago

thanks for the answer but i have multi columns for sort ex - SELECT * FROM fltable ORDER BY Traffic DESC,Speed ASC,Cost ASC , do you know how to use this with above query(traffic=fractions), thanks again.

Collectives™ on Stack Overflow

MySQL PHP order by

2 Answers 2

1 Comment

Result with `num` column

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Result with num column

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related

Result with `num` column