CODEWITHSUNDEEP
mysql
Azeem Haider
Azeem Haider

Reputation: 1513

Group and order rows with multiple column MySQL

I want to rows according to same column value.
Suppose this is a table 

id    name   topic 
1     A      t
2     B      a
3     c      t
4     d      b
5     e      b
6     f      a

I want result something like this.

id    name   topic
1     A       t
3     c       t
2     B       a
6     f       a
4     d       b
5     e       b

As you can see these are not order by topic neither by id, it sort about that topic which come first if t come first sort t first, one second a come then sort according to a then b.

if you apply ORDER BY topic it sort a b t or in DESC t b a but required result is t a b

Any suggestion ?

Upvotes: 0

Views: 24

Answers (2)

Strawberry
Strawberry

Reputation: 33935

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,topic CHAR(1) NOT NULL
);

INSERT INTO my_table VALUES
(1,'t'),
(2,'a'),
(3,'t'),
(4,'b'),
(5,'b'),
(6,'a');

SELECT x.* 
  FROM my_table x 
  JOIN 
     ( SELECT topic, MIN(id) id FROM my_table GROUP BY topic ) y 
    ON y.topic = x.topic
 ORDER 
    BY y.id,x.id;
+----+-------+
| id | topic |
+----+-------+
|  1 | t     |
|  3 | t     |
|  2 | a     |
|  6 | a     |
|  4 | b     |
|  5 | b     |
+----+-------+

Upvotes: 1

Ullas
Ullas

Reputation: 11556

You can use CASE expression in ORDER BY.

Query

select * from `your_table_name`
order by 
  case `topic` 
  when 't' then 1 
  when 'a' then 2 
  when 'b' then 3 
  else 4 end
, `name`;

Upvotes: 1

Related Questions