CODEWITHSUNDEEP
mysqlsorting
OcuS
OcuS

Reputation: 5310

Mysql "complex" sort

Imagine that you have 2 tables like this:

mysql> SELECT * FROM theme;
+----+---------+------------+
| id | name    | sort_order |
+----+---------+------------+
|  1 | Theme 1 | 1          |
|  2 | Theme 2 | 2          |
|  3 | Theme 3 | 3          |
|  4 | Theme 4 | 4          |
|  5 | Theme 5 | 5          |
|  6 | Theme 6 | 6          |
|  7 | Theme 7 | 7          |
+----+---------+------------+

mysql> SELECT * FROM article;
+----+------------+---------------------+----------+
| id | title      | update_date         | theme_id |
+----+------------+---------------------+----------+
|  1 | Article 1  | 2012-06-29 15:29:50 |        6 |
|  2 | Article 2  | 2012-07-18 00:00:00 |        2 |
|  3 | Article 3  | 2012-07-19 00:00:00 |        4 |
|  4 | Article 4  | 2012-07-18 00:00:00 |        4 |
|  5 | Article 5  | 2012-07-18 00:00:00 |        1 |
|  6 | Article 6  | 2012-06-26 10:30:51 |        6 |
|  7 | Article 7  | 2012-07-18 15:17:08 |        6 |
|  8 | Article 8  | 2012-06-18 00:00:00 |        4 |
|  9 | Article 9  | 2012-07-18 15:48:28 |        1 |
| 10 | Article 10 | 2012-07-09 00:00:00 |        4 |
+----+------------+---------------------+----------+

Each article is bound to one-and-only-one theme.

You want to be able to execute a query that gives you a list of article ordered like this:

For the current data, it should give the following:

+----+------------+---------------------+----------+
| id | title      | update_date         | theme_id |
+----+------------+---------------------+----------+
|  9 | Article 9  | 2012-07-18 15:48:28 |        1 |
|  2 | Article 2  | 2012-07-18 00:00:00 |        2 |
|  3 | Article 3  | 2012-07-19 00:00:00 |        4 |
|  7 | Article 7  | 2012-07-18 15:17:08 |        6 |
|  5 | Article 5  | 2012-07-18 00:00:00 |        1 |
|  4 | Article 4  | 2012-07-18 00:00:00 |        4 |
|  1 | Article 1  | 2012-06-29 15:29:50 |        6 |
| 10 | Article 10 | 2012-07-09 00:00:00 |        4 |
|  6 | Article 6  | 2012-06-26 10:30:51 |        6 |
|  8 | Article 8  | 2012-06-18 00:00:00 |        4 |
+----+------------+---------------------+----------+

I'm almost sure there is a way to do this using a single query but I can't figure it out.

How would you achieve this ?

Upvotes: 3

Views: 514

Answers (2)

Andriy M
Andriy M

Reputation: 77657

This is related to the problem of partitioned ranking in MySQL. There are no windowed ranking functions in MySQL, but the generic problem can successfully be solved with the help of variables:

SELECT
  id,
  title,
  update_date,
  theme_id
FROM (
  SELECT
    *,
    @rnk := @rnk * (@last_theme = theme_id) + 1 AS rnk,
    @last_theme := theme_id
  FROM article, (SELECT @rnk := 0, @last_theme := 0) s
  ORDER BY theme_id, update_date DESC
) s
ORDER BY
  rnk, theme_id
;

The above query both ranks the rows and then uses the rankings to sort the final result set. The query first retrieves rows from article ordering them by theme_id and update_date DESC to assign ranking numbers. Then, when selecting from the ranked row set, another, final, ordering is introduced, this time by the rankings and theme_id.

You can try this query at SQL Fiddle.

Upvotes: 5

ethrbunny
ethrbunny

Reputation: 10469

Could you do a 'article join theme' and then order by update_date, sort_order?

Upvotes: 0

Related Questions