How can I sort two SELECT query and combine them

Question

I've a table like below which holds user records. I want to list the users whose permission field is 0 first and then the users with permission 1. But I also want to sort them alphabetically.

This is my table:

users
--------------------------------
user_id     name     permission
1           jack      0
2           anne      0
3           kate      0
4           steve     1
5           roger     0
6           judy      1
7           robin     0
8           stella    1
9           arthur    0

And I want to get the this result:

users
---------------------------------
user_id       name       permission
2             anne       0
9             arthur     0
1             jack       0
3             kate       0
7             robin      0
5             roger      0
6             judy       1
8             stella     1
4             steve      1

As you can see, there are two groups, first the users with permission "0" and later permission "1". But each group is also sorted alphabetically in itself.

(
SELECT * 
FROM  `users` 
ORDER BY name ASC
)
UNION (

SELECT * 
FROM  `users` 
ORDER BY name ASC
)
ORDER BY permission ASC

I've tried to get the two groups separately by sorting and combine them but it is not working in the way I want.

Answer 1

A code like below works

SELECT * 
FROM  `users` 
ORDER BY permission ASC , username ASC

you should write the name of the column that you want to initially order by first, then the second, third, etc

Answer 2

SELECT 
 * 
FROM users
ORDER BY 
        permission ASC, 
        name ASC;

You don't need two separate queries

First the result set is sorted in ascending order (from lower to higher) based on the value of permission column.

Now you have a sorted list where all the records having 0 as permission will appear first.

Next, if you apply another sort on name column on this sorted list then it will make an alphabetical sorted list where the previous sorting order holds as well as this one.

---

Test:

create table users(
  id int primary key AUTO_INCREMENT,
  name varchar(50),
  permission int
 );

INSERT INTO users(name,permission)
VALUES ('A',1),('D',0),('C',0),('B',1);

SELECT * FROM users;

id     name   permission

1        A       1
2        D       0
3        C       0
4        B       1

---

#Sort only by permission (ASC)

SELECT * FROM users ORDER BY permission ASC;

id     name   permission

2        D       0
3        C       0
1        A       1
4        B       1

---

#Sort first by permission (ASC), then by name (ASC)

SELECT * FROM users ORDER BY permission ASC, name ASC;

id     name   permission

3        C       0
2        D       0
1        A       1
4        B       1

---

SEE DEMO