Pushing Items to end of stack (part 2)

Question

I have the following code which gets items to present to a user:

SELECT * FROM main_cue WHERE group_id IN (1,2,3,4) GROUP BY group_id;
>>> 1,2,3,4

However, I want to provide a specific ordering to this, such that (1) I have a list of items to push to the end of the stack In the exact ordering I specify and (2) the items that are not in the list will be ordered by id ASC. For example:

items_to_push_to_end_of_stack_in_this_exact_order = [3,2]

The new ordering would then be:

>>> [1,4,3,2]
1) 1 # first item by ID asc, not in `items_to_push_to_end_of_stack_in_this_exact_order`
2) 4 # second item by ID asc, not in `items_to_push_to_end_of_stack_in_this_exact_order`
3) 3 # items_to_push_to_end_of_stack_in_this_exact_order
4) 2 # items_to_push_to_end_of_stack_in_this_exact_order

How would I do this in mysql?

possible duplicate of [MySQL sort by some list](http://stackoverflow.com/questions/6810793/mysql-sort-by-some-list) — Cfreak, Dec 13 '14 at 23:13
@Cfreak I think the above question is not just using the `ORDER BY FIELD(something)` that the other question and answers use. This is more of a composite sorting, using that method along with others. — David542, Dec 13 '14 at 23:28

score 1 · Accepted Answer · edited Dec 13 '14 at 23:25

If you are trying to specify the ordering, then use order by:

ORDER BY

    // First, Put "2" and "3" at the end of the stack
   (group_id IN (2, 3)) ASC,

   // Second, order the items not in the list by the `group_id`
   (CASE WHEN group_id NOT IN (2, 3) THEN group_id END) ASC,

   // Third, order the items in the list by the ordering you specify
   FIELD(group_id, 3, 2)

This is a bit complicated. The first clause puts "2" and "3" at the end. The second orders the rest by group_id. The third orders the last set by the ordering you specify.

score 0 · Answer 2 · answered Dec 13 '14 at 23:27

Using Gordon's answer and the code I currently have, this is what worked:

passed_on_group_ids_as_str = ','.join(passed_on_group_ids) # convert the list to csv

cues_per_group = Cue.objects.raw('''SELECT * FROM main_cue WHERE group_id IN %s GROUP BY group_id 
                                        ORDER BY (group_id IN (%s)) ASC,
                                        (CASE WHEN group_id NOT IN (%s) THEN group_id END) ASC,
                                        FIELD(group_id, %s)''', 
                                    (group_ids, passed_on_group_ids_as_str, passed_on_group_ids_as_str, passed_on_group_ids_as_str))

Pushing Items to end of stack (part 2)

2 Answers2