Multiple mysql ORDER BY's for multidimensional

问题:

I have a set of data that needs to be ordered in multiple ways simultaneously.

Simplified, the table can be summarised as:

Task

• id
• group (int)
• date (date)
• complete (0/1)

I need to produce a list of these tasks, sorted primarily by 'complete', secondarily by 'date' and then grouped by 'group'.

The group is what's causing the issue for me and I have a feeling this might be better achieved with PHP, as it may not be possible as part of the query - tips on the best way to approach this are welcome, it certainly doesn't have to be pure mysql.

Currently I'm using:

ORDER BY complete, group, date

This works perfectly for 'ungrouped' tasks, all the not complete (0) tasks are at the top, with the complete (1) tasks at the bottom; with each set of complete/not complete tasks sorted by date.

However a group can end up bunched at the bottom, with the date ordering incorrect in the wider context - the group itself isn't placed given its tasks dates. Here's an example of the unwanted output (ordered):

Task #2 - Group(false), Complete(0), date(2013-11-01)
Task #4 - Group(false), Complete(0), date(2013-12-01)
Task #5 - Group(1), Complete(0), date(2013-10-01)
Task #3 - Group(1), Complete(0), date(2013-12-01)
Task #1 - Group(false), Complete(1), date(2013-11-01)

As you can see the date ordering is incorrect for the grouped items, with the ordering taking place within the group. Task #5 is placed third, even though it has the earliest date.

The output I'd like to see is as follows:

Task #5 - Group(1), Complete(0), date(2013-10-01)
Task #3 - Group(1), Complete(0), date(2013-12-01)
Task #2 - Group(false), Complete(0), date(2013-11-01)
Task #4 - Group(false), Complete(0), date(2013-12-01)
Task #1 - Group(false), Complete(1), date(2013-11-01)

i.e. if a task within a group has a date earlier than an individual task, the whole group should be ordered first (imagine that with a group you only see the 'top' task, so task #3 would be collapsed, visually).

I've tried changing the order of the 'ORDER BY' clause, but it doesn't seem that any combination achieves what I'm after - something always ends up in the wrong place.

Any help greatly appreciated.

回答1:

How about something like:

SELECT t1.ID, t1.`Group`, t1.Complete, t1.Due 
FROM task t1
LEFT JOIN (
    SELECT Complete, 
        t.`Group`, 
        MIN(Due) AS MinDate 
    FROM task t
    GROUP BY Complete, t.`Group` ) t2 ON t1.complete = t2.complete AND t1.`Group`  = t2.`Group`     
ORDER BY t1.complete, IFNULL(t2.MinDate, t1.Due), `Group`, t1.Due 

For each Group record, join it to the earliest record from that group, then you can order by the earliest group date (and if not grouped, then just the date).

SQLFiddle

回答2:

So you want to sort by the earliest date in the group and then keep the whole group together?

You can use an aggregation on YourTable to get the lowest date per group. You can then sort primarily on that minimum date and then on the other fields.

select
  t.id,
  t.group,
  t.date,
  t.complete
from
  YourTable t
  left join
    (select
      m.group,
      min(m.date) as mindate
    from
      YourTable m) mt on mt.group = t.group
order by
  /* Coalesce for individual tasks, which don't have a mindate */
  coalesce(mt.mindate, t.date),
  t.group,
  t.complete,
  t.date

You can then start playing around to include other fields (like Complete) in that aggregation. For instance, this query should start with groups that contain incomplete tasks and incomplete individual tasks. After that come the completed tasks and the groups that only contain completed tasks. Within the groups, a sorting is still applies that puts incomplete tasks on above completed tasks and after that sorts them by date. As you can see, a lot of extra logic for only a small change to the query:

select
  t.id,
  t.group,
  t.date,
  t.complete
from
  YourTable t
  left join
    (select
      m.group,
      min(m.date) as mindate,
      min(m.complete) as groupcomplete
    from
      YourTable m) mt on mt.group = t.group
order by
  coalesce(mt.groupcomplete, t.complete),
  coalesce(mt.mindate, t.date),
  t.group,
  t.complete,
  t.date