RANKING IN MYSQL RESULTS

By admin, 14 五月, 2015

A friend of me asked me long time ago: "How can I have a ranking on a result with MySQL?". Now I found some time to write it down:

Lets do first some preparation for the example:

CREATE TABLE sales (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, fruit VARCHAR(32)
, amount DECIMAL
);

INSERT INTO sales
VALUES (NULL, 'apple', 12.75), (NULL, 'orange', 1.89), (NULL, 'pear', 19.23)
, (NULL, 'banana', 4.25), (NULL, 'cherry', 123.75), (NULL, 'plum', 23.15)
;

Now lets query:

SELECT fruit, amount
FROM sales
ORDER BY amount DESC
;

+--------+--------+
| fruit | amount |
+--------+--------+
| cherry | 124 |
| plum | 23 |
| pear | 19 |
| apple | 13 |
| banana | 4 |
| orange | 2 |
+--------+--------+

Hmmmm...., this not yet what we want!

And now with ranking:

SET @rank=0;

SELECT @rank:=@rank+1 AS rank, fruit, amount
FROM sales
ORDER BY amount DESC
;

+------+--------+--------+
| rank | fruit | amount |
+------+--------+--------+
| 1 | cherry | 124 |
| 2 | plum | 23 |
| 3 | pear | 19 |
| 4 | apple | 13 |
| 5 | banana | 4 |
| 6 | orange | 2 |
+------+--------+--------+

Much better!

An other possibility would be, to do the whole ranking in the application (on the application server).

If you liked this article and if you have some more MySQL related questions consider our Consulting Services!

Reference: http://www.fromdual.com/ranking-mysql-results

标签

评论

Restricted HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id> <img src>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。
验证码
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
请输入"Drupal10"