0

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.

Nathan Hornby
  • 1,423
  • 16
  • 32
  • Please show us your complete query. – John V. Nov 11 '13 at 13:20
  • Aside from the 'ORDER BY' it's just a basic select query with a 'where' relating to a column not mentioned or relevant to the question. (who's idea was it to have 'return' submit these comments? Infuriating). – Nathan Hornby Nov 11 '13 at 13:32

2 Answers2

1

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
GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • This looks positive! I'm just going to look at converting this into CodeIgniter's Active Record syntax and give it a go. – Nathan Hornby Nov 11 '13 at 13:29
  • Annoyingly I don't think I'll have a chance to look at this until tomorrow, so I'll update (and hopefully mark as the correct answer) then! – Nathan Hornby Nov 11 '13 at 16:33
  • Unfortunately I'm struggling to get this into CI's Active Record syntax and have a question open to help with that side of things, if you have any experience with CI please feel free to take a look! http://stackoverflow.com/questions/19931256/mysql-query-into-code-igniter-active-directory-format – Nathan Hornby Nov 12 '13 at 14:09
  • I don't know much about CI's Active Record syntax, but I think I still found your error. I've provided an answer. :) But besides, it is a good idea to get a tool like Toad or PHPMyAdmin so you can develop and test queries before migrating them to Code Igniter. That way you can validate if the error is in fact in the migration or in the query someone on SO gave you. ;) – GolezTrol Nov 12 '13 at 14:35
  • Normally CI's syntax is faster than checking through PHPMyAdmin - however in this case that doesn't seem to be true :) I think your guess was correct, however there are some still some minor syntax issues, not relating to your answer though. – Nathan Hornby Nov 12 '13 at 14:48
  • OK we're back on track - new issue (but relating to your answer!). I'm getting an error telling me that "Unknown column 't.complete' in 'field list'" - however I think this is a dependency thing based on the order that the CI syntax takes (as 't' hasn't been defined yet, which you can see in that other question I had where I showed the CI syntax of your above query). Removing the t doe actually enable the query to complete, but gives me no results. EDIT: I tried running your query in phpMyAdmin and get the same error actually, "#1054 - Unknown column 't.complete' in 'field list'" – Nathan Hornby Nov 12 '13 at 15:14
  • I tried scaling it back to your first query, keeping things a little simpler, this one works but I get 0 results :( – Nathan Hornby Nov 12 '13 at 15:26
  • I see that I made a typo and typed t.comlete (without a 'p'). Have you spotted that as well? If the query returns 0 rows, that is odd, since my first query doesn't do any filtering and should return exactly the number of rows in YourTable. – GolezTrol Nov 12 '13 at 18:03
  • Ah, maybe that was part of the issue - it does seem to be returning results now - however they're not in a desirable order, all that seems to have done is ordered by date http://imgur.com/5pazxhN - image shows columns group-date-complete (as in I could just use `ORDER BY date` and it would produce identical results). I'll see if I can get your second example working in phpmyadmin! – Nathan Hornby Nov 13 '13 at 10:20
  • I've just tried with the second query again, checking for typo's etc. And I'm still getting `#1054 - Unknown column 't.complete' in 'field list'` in phpmyadmin. – Nathan Hornby Nov 13 '13 at 10:37
  • My bad again. The inner query uses the alias m, so `min(t.complete)` should be `min(m.complete)`. Shameless copy-paste error. I didn't have an actual database here, so I just typed it in Notepad. Forgive me. ;) – GolezTrol Nov 13 '13 at 13:52
  • Forgiven!:) The other answer ended up working a treat so I've implemented that. I see there are some differences in approach - if you wanted to share any pro's/con's of the differences I'd be happy to hear them! If not then consider it solved. – Nathan Hornby Nov 13 '13 at 14:20
  • It looks like the same approach, only with a slightly different sort order. `COALESCE` supports more than 2 values and is portable between databases, in contrast to `IFNULL`, which only supports 2 values and is specific to MySQL. Apart from that there's not much to discuss. – GolezTrol Nov 13 '13 at 19:49
  • Oh, that's not entirely true. That query groups the inner query by group and complete. That means that the subquery returns two rows for each group which contains both open and completed items. That query also joins by `complete` first. That means that completed tasks will be on top, while incompleted tasks will be at the bottom. Apart from that, tasks of a group are not kept together. I think my query should do that correctly, or maybe I misunderstood your requirements. – GolezTrol Nov 13 '13 at 19:53
  • I'm not sure even I know anymore… however the query I've implemented based on the other answer does in fact group completed (1) tasks at the bottom and grouped tasks are in fact kept together. Either way it works :) – Nathan Hornby Nov 14 '13 at 14:24
  • Ah yeah, makes sense. If forgot 0 comes before 1. :o Anyway, good thing it's fixed now. :) – GolezTrol Nov 14 '13 at 15:09
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

OGHaza
  • 4,795
  • 7
  • 23
  • 29
  • Unfortunately this is just producing syntax errors for me: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group, MIN(due) AS MinDate FROM task GR' at line 5" (note that I changed 'date' to 'due', and 'task' is the name of the table) – Nathan Hornby Nov 12 '13 at 15:59
  • I've altered the answer to use t.Group instead of just Group (- had to change the `FROM table` line to `FROM table t` too). Hopefully thats enough to get past that error. Otherwise try putting back ticks \`\` around every mention of the Group column - I can see 6. That looks like its because the column Group is being parsed as GROUP as in the start of GROUP BY. – OGHaza Nov 12 '13 at 16:06
  • Thanks, that did fix that error! new one: "#1582 - Incorrect parameter count in the call to native function 'ISNULL'" - any thoughts? – Nathan Hornby Nov 12 '13 at 16:33
  • whoops yup, its meant to be IFNULL (I'm using sql-server here which uses ISNULL) - updating answer now. just fixed a spelling error in line 8 where complete was misspelled. (Getting increasingly concerned I might not have tested this, I think I did though) – OGHaza Nov 12 '13 at 16:34
  • That gets the query working - however unfortunately it produces the exact same results as my unwanted example in my question. i.e. the result is: Tasks sorted by date, then groups (after the other tasks, irrelevant of dates) and then completed tasks - it doesn't slip the group into the appropriate place in the date range. So it doesn't seem to be doing anything more than `ORDER BY complete, group, date` – Nathan Hornby Nov 13 '13 at 10:15
  • urgh... I'll have a look now – OGHaza Nov 13 '13 at 10:19
  • Ok updated my answer, put a SQLFiddle there as well to demonstrate it - (check the results in the Fiddle are what you're looking for) – OGHaza Nov 13 '13 at 10:35
  • Your example works perfectly, unfortunately a minor change (which is required) seems to break it, see here: http://sqlfiddle.com/#!2/263ba/1 I changed the group from BIT to INT, and gave it a value of 3 - for some reason this confuses it. As you'll see unfortunately it lists the group first, even though there is a task that's due the month before the first grouped task. Any ideas? – Nathan Hornby Nov 13 '13 at 11:18
  • Right, I've made a right mess of this. Check the new fiddle/code – OGHaza Nov 13 '13 at 11:24
  • Perfect! I've just got to get it into CI's Active Record syntax now, but looks straight forward enough. Thanks for your time and effort @OGHaza – Nathan Hornby Nov 13 '13 at 11:30
  • No worries, got there eventually ;) – OGHaza Nov 13 '13 at 11:41