Mysql "complex" sort

Question

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:

the first most recent article for each theme ordered by theme's sort_order
the second most recent article for each theme ordered by theme's sort_order
the third most recent article for each theme ordered by theme's sort_order
and so on...

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 ?

Create separate queries for each of your needed case and put them all together with UNION — Nobwyn
– Nobwyn, Commented Jul 19, 2012 at 13:41
@Laimoncijus: There is only one case: most recent article for each theme (if any) then second most recent article for each theme (if any) and so on... — OcuS
– OcuS, Commented Jul 19, 2012 at 13:48
I think you have to try unions then. I don't know the exact sql, I need to test the data on my db. — Panagiotis
– Panagiotis, Commented Jul 19, 2012 at 14:19

Andriy M · Accepted Answer · 2012-07-19 14:25:58Z

5

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.

edited Jul 19, 2012 at 14:25

answered Jul 19, 2012 at 14:19

Andriy M

78k18 gold badges100 silver badges157 bronze badges

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

Comments

ethrbunny · Accepted Answer · 2012-07-19 13:41:36Z

0

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

answered Jul 19, 2012 at 13:41

ethrbunny

10.5k9 gold badges77 silver badges145 bronze badges

1 Comment

OcuS Over a year ago

No because in this case I will get all the recent articles first regardless of the theme.

Collectives™ on Stack Overflow

Mysql "complex" sort

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related