MySQL 8.0.22 | Select Order By (ASC, DESC)

Order by — The clause that can put tuples by certain orders.

[Order By]

There are few ways to use order by, but today I’m going to show you the main two of them.

(1) ASC

You can use ASC when you want to return the data in ascending order.(1,2,3…)
select select_list from tableName order by columnName asc;

(2) DESC

DESC stands for descending. (…3,2,1)
select select_list from tableName order by columnName desc;

[Practice Questions]

As always, you can copy and past the query down below, but let’s try to solve the problems by your self as much as you can. I bet you can do that!

create table user(
id int primary key,
name varchar(8),
point int
);
insert into user values
(1,'Nina',800),
(2,'Tom',600),
(3,'Amy',800),
(4,'Jake',600),
(5,'Lisa',200);
***Result***
mysql> select * from user;
+----+------+-------+
| id | name | point |
+----+------+-------+
|  1 | Nina |   800 |
|  2 | Tom  |   600 |
|  3 | Amy  |   800 |
|  4 | Jake |   600 |
|  5 | Lisa |   200 |
+----+------+-------+
5 rows in set (0.00 sec)

Q1. Return all members information by their point in ascending order.

mysql> select * from user order by point asc;
+----+------+-------+
| id | name | point |
+----+------+-------+
|  5 | Lisa |   200 |
|  2 | Tom  |   600 |
|  4 | Jake |   600 |
|  1 | Nina |   800 |
|  3 | Amy  |   800 |
+----+------+-------+
5 rows in set (0.00 sec)

You can put asc at the end, but the default order by is asc, so it’s fine if you didn’t.

Q2. Return all the members information by their id in descending order.

mysql> select * from user order by id desc;
+----+------+-------+
| id | name | point |
+----+------+-------+
|  5 | Lisa |   200 |
|  4 | Jake |   600 |
|  3 | Amy  |   800 |
|  2 | Tom  |   600 |
|  1 | Nina |   800 |
+----+------+-------+
5 rows in set (0.00 sec)

Q3. Return members id, name, point by their point in ascending order, but if there are same point, put them by id in descending order.

mysql> select * from user order by point asc, id desc;
+----+------+-------+
| id | name | point |
+----+------+-------+
|  5 | Lisa |   200 |
|  4 | Jake |   600 |
|  2 | Tom  |   600 |
|  3 | Amy  |   800 |
|  1 | Nina |   800 |
+----+------+-------+
5 rows in set (0.00 sec)

If you need more than one order, you can simply add ,column asc/desc at the end. The first column after by is the main order, and the second is the next one.

Today was very simple right? On my next session I’ll bring some more clause that you can use with select statement as well. Thanks guys see ya!