CODEWITHSUNDEEP
mysql
AdamM
AdamM

Reputation: 70

MySQL order by multiple columns (id and integer)

I have a table that is structured as per the id (101, 102, 201, 301, etc.) with the first digit a navigational column, and with a placement value (1, 2, 3, and up) for knowing where the items in the navigation goes.

Here is how the table is structured in hind sight:

id   title   placement
101   abc      3
102   def      1
103   ghi      2
201   jkl      2
202   mno      3
203   pqr      1
301   stu      2
302   vwx      1

How I would like the table structured is the following:

id   title   placement
102   def      1    
103   ghi      2
101   abc      3
203   pqr      1
201   jkl      2
202   mno      3
302   vwx      1
301   stu      2

Notice how the placement is asc first,followed by the id.

I thought at first it would be a simple order by id, placement; But it's not.

I've tried both combinations of Group By and Order By, followed by a Union and a Field set value, but none worked to my knowledge.

Here is some querys I went through with no luck getting what I want:

SELECT t.* FROM (SELECT * FROM table_name ORDER BY placement) t GROUP BY t.id

and

(SELECT * FROM table_name ORDER BY placement)
UNION
(SELECT * FROM table_name ORDER BY id);

I also did a join but I can't remember the query.

Thanks for any additional help.

Upvotes: 0

Views: 53

Answers (1)

univerio
univerio

Reputation: 20548

It sounds like you want to order by the first digit of id first, then placement:

SELECT *, (id DIV 100) AS id_first_digit FROM t ORDER BY id_first_digit, placement;

You probably want to modify the expression for id_first_digit depending on your id format (e.g. if it can be four digits).

Upvotes: 1

Related Questions