MySQL order by multiple columns while grouping by one

Question

So I have a weird scenario where I want the following data to be ordered in a certain way. Let the table data be:

abc 111 2     priority
abc 111 blah  data
abc 222 1     priority
abc 222 blah  data
abc 333 3     priority
abc 333 blah  data

I want to order that data based on column three (where column 4 is priority) but keep the return order grouped by column 2. So the expected query result would look like:

abc 222 1     priority
abc 222 blah  data
abc 111 2     priority
abc 111 blah  data
abc 333 3     priority
abc 333 blah  data

What's the best possible way of doing this. I can think of doing a query up front and an in clause, but then I would have to account for all possible priorities.

Thanks in advance. FYI, its MySQL that I am using.

Answer 1

I think this is what you need:

http://sqlfiddle.com/#!2/48057/14

DDL

CREATE TABLE my_data( 
  name VARCHAR(20),
  num NUMERIC(5), 
  c NUMERIC(3),
  priority NUMERIC(3)
);

DML

INSERT INTO my_data VALUES("abc", 111, 2, 1);
INSERT INTO my_data VALUES("abc", 222, 3, 4);
INSERT INTO my_data VALUES("abc", 222, 1, 9);
INSERT INTO my_data VALUES("abc", 111, 4, 2);

Answer 2

It would be better if you could have actually include actual column names even with bogus data, however, I've named them corresponding with the type of content I think you are presenting.

It appears your second column is some "ID" column and want to keep them all grouped together. So, pre-query that in the order you want but only for the "Priority" column ignoring the rest of the records. THEN, using MySQL variables, you can assign it a sequential value for final output. Then join to the regular data on the "ID" column for all other values... Something like...

select
      md2.ABCColumn,
      SortSeqQuery.IDColumn,
      md2.DescripColumn,
      md2.ColumnWith123Sequence
   from
      my_Data md2
         LEFT JOIN ( select 
                           md1.IDColumn,
                           @SeqVal := @SeqVal +1 as FinalSortOrder
                        from
                           my_Data md1,
                           ( select @SeqVal := 0 ) sqlvars
                        where
                           md1.DescripColumn = "priority"
                        order by
                           md1.ColumnWith123Sequence,
                           md1.IDColumn ) SortSeqQuery
            on md2.IDColumn = SortSeqQuery.IDColumn 
   order by
      case when SortSeqQuery.IDColumn is null then 2 else 1 end,
      coalesce( SortSeqQuery.FinalSortOrder, 0 ) as FinalSort

With the "Order By", it pre-sorts the qualified data BEFORE it actually applies the @SeqVal which in turn should come back as 1, 2, 3, 4, etc...

The "SortSeqQuery" will be run first to pre-qualify any "priority" records and have them sorted and available. Then, your "my_data" table is basis of the query and joins to that result set and grabs the appropriate sort sequence based on matching ID column. If there are IDColumn entries that DO NOT have a priority, then they will also be included, but with the order by based on a case/when, any that are null are pre-sorted to the bottom, those found will be sorted to the top. Within that sort, it will keep all IDColumn entries grouped together since the same "FinalSortOrder" is directly associated to the IDColumn in the preliminary query.

Answer 3

ANSWER -

CREATE TABLE my_data( name VARCHAR(20),groupid INT(5), attributekey VARCHAR(10),attributevalue VARCHAR(10));

INSERT INTO my_data VALUES("abc", 111, "priority", "2");
INSERT INTO my_data VALUES("abc", 111, "someattr", "blah");
INSERT INTO my_data VALUES("abc", 222, "priority", "1");
INSERT INTO my_data VALUES("abc", 222, "someattr", "blah");
INSERT INTO my_data VALUES("abc", 333, "priority", "3");
INSERT INTO my_data VALUES("abc", 333, "someattr", "blah");

solution -

SELECT m1.*,
(select attributevalue from my_data m2
   where m1.groupid=m2.groupid 
   AND m2.attributekey='priority' 
) groupidpriority 
FROM my_data m1
order by groupidpriority;