Group and order rows with multiple column MySQL

Question

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 ?

So you want to sort rows according to the name associated with the 'first' occurrence of each topic? — Strawberry
– Strawberry, Commented Oct 4, 2017 at 12:52

Strawberry · Accepted Answer · 2017-10-04 12:56:56Z

1

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     |
+----+-------+

answered Oct 4, 2017 at 12:56

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Strawberry · Accepted Answer · 2017-10-04 10:12:39Z

1

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`;

edited Oct 4, 2017 at 10:12

Strawberry

34k14 gold badges43 silver badges58 bronze badges

answered Oct 4, 2017 at 10:10

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

3 Comments

Azeem Haider Over a year ago

But what if I don't know the names of topic, Suppose user randomly enter any topic

Strawberry Over a year ago

@AzeemHaider If you don't know the names then how can you know the order?

Azeem Haider Over a year ago

Exactly that is my question, If you see id col there t start from id 1 a start from id 2 and b start from id 4 this define the order if somewho we implement this

Collectives™ on Stack Overflow

Group and order rows with multiple column MySQL

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related