Geek Answers Handbook

MySQL sort for calculation

Is it possible to sort by calculation 2 rows in mySQL? For example, I have 2 lines, lp and ap I'm trying to do something like this:

 SELECT * from myTbl WHERE 1 ORDER BY (lp/ap)

Which does not cause an error, but also does not sort the results of this calculation. Is there a way to do this, or do I need to store lp / ap in the database?

+7
sorting mysql
source share
3 answers

Yes, it is possible, and it really works. Check out the following test:

 CREATE TABLE a(a INT, b INT); INSERT INTO a VALUES (1, 1); INSERT INTO a VALUES (1, 2); INSERT INTO a VALUES (1, 3); INSERT INTO a VALUES (1, 4); INSERT INTO a VALUES (1, 5); INSERT INTO a VALUES (1, 6); INSERT INTO a VALUES (2, 1); INSERT INTO a VALUES (2, 2); INSERT INTO a VALUES (2, 3); INSERT INTO a VALUES (2, 4); INSERT INTO a VALUES (2, 5); INSERT INTO a VALUES (2, 6); SELECT aa, ab, (a/b) FROM a ORDER BY (a/b); +------+------+--------+ | a | b | (a/b) | +------+------+--------+ | 1 | 6 | 0.1667 | | 1 | 5 | 0.2000 | | 1 | 4 | 0.2500 | | 2 | 6 | 0.3333 | | 1 | 3 | 0.3333 | | 2 | 5 | 0.4000 | | 1 | 2 | 0.5000 | | 2 | 4 | 0.5000 | | 2 | 3 | 0.6667 | | 2 | 2 | 1.0000 | | 1 | 1 | 1.0000 | | 2 | 1 | 2.0000 | +------+------+--------+

SELECT aa, ab FROM a ORDER BY (a/b); will return the same results.

+17
source share
  SELECT *, (lp/ap) AS calculation from myTbl ORDER BY calculation

This should do the trick if (lp / ap) is valid.

+9
source share

you are likely to be more likely to do

 SELECT *, (lp/ap) as n from myTbl ORDER BY n
+2
source share

More articles:


All Articles