MySQL ORDER BY Descending ASC command for listing of records

The results we get from a table we may have to display in an order. The result may be from highest to lowest or lowest to highest in a numeric field or from A to Z or Z to A in a text or varchar field. We may require a combination of this also.

Order by MySQL query to display rows based on order of columns

SELECT * FROM STUDENT ORDER BY MARK;

id	name	class	mark	gender
19	Tinny	Nine	18	male
17	Tumyu	Six	54	male
29	Tess Played	Seven	55	male
3	Arnold	Three	55	male
6	Alex John	Four	55	male
22	Reggid	Seven	55	female
10	Big John	Four	55	female
4	Krish Star	Four	60	female
5	John Mike	Four	60	female
20	Jackly	Nine	65	female

Highest to lowest order

We can apply this to our numeric field mark to display the list in order of lowest mark to highest mark by using the ASC command ( ascending command ) . Please note that by default all order by commands are in ascending order only. Here is the command to display the records in descending order ( from highest to lowest ) based on the mark field.

SELECT * FROM `student` ORDER BY mark DESC;

Here the total mark will be displayed in the order of highest to lowest and class will not be considered. To display the records in order of mark in a class, we have to use to field names in the order by clause. Here is the code to display the records in the order of class and then in the order of marks. This will give a highly use full way by displaying all the records in order of class and within the class in order of marks.

SELECT * FROM `student` ORDER BY class DESC, mark DESC;

id	name	class	mark
2	Max Ruin	Three	85
3	Arnold	Three	55
1	John Deo	Four	75
4	Krish Star	Four	60
5	John Mike	Four	60
6	Alex John	Four	55

SELECT * FROM `student` ORDER BY class ASC, mark DESC;

Read how Order by command is used to display highest 3 records of student table

order by on a varchar field

Here is the command to display the records in the order of class

SELECT * FROM `student` ORDER BY class

id	name	class	mark
1	John Deo	Four	75
4	Krish Star	Four	60
5	John Mike	Four	60
6	Alex John	Four	55
2	Max Ruin	Three	85
3	Arnold	Three	55

Here the records are returned in the order by class from A to Z . We can reverse the order by specifying in our sql command to change the order to Descending that is from Z to A. We have to add desc to the ORDER BY clause. Here is the command to do that

SELECT * FROM `student` ORDER BY class DESC

Order by more than one column of a table

We can display list of students based on their mark and based on their name.

SELECT * FROM student ORDER BY mark, name

This will list on ascending order of mark. When there are more than one student got the same mark ( say 88 ) then the names of them will be listed alphabetically.

So the list will display Bigy above Giff Tow though both of them got equal mark ( say 88 )

If you want to reverse the order of Names you can change the query like this

SELECT * FROM student ORDER BY mark, name DESC

If you want listing should be from highest mark to lowest mark then query should be

SELECT * FROM student ORDER BY mark DESC, name DESC

How to use in PHP Script

You can read more on SQL SELECT query to see how this query can be used in PHP Script to display records. Only the query part is required to be changed.

order by string as integer

While listing in order by any VARCHAR or string column having number, we need to convert them to number by using CAST and then use order by query.
Create one more column diff , VARCHAR ( 3 ) then update it by using this query

UPDATE student SET diff = 100 - mark

Now use order by query.

SELECT * FROM `student` ORDER BY diff DESC

You will see the list like this.

id	name	class	mark	gender	diff
19	Tinny	Nine	18	male	82
12	Recky	Six	94	female	6
17	Tumyu	Six	54	male	46
22	Reggid	Seven	55	female	45
29	Tess Played	Seven	55	male	45
3	Arnold	Three	55	male	45
6	Alex John	Four	55	male	45
10	Big John	Four	55	female	45
4	Krish Star	Four	60	female	40
5	John Mike	Four	60	female	40

This is listed based on the first char of diff column.
We need to change the column value by using CAST and then use order by

SELECT * FROM `student` ORDER BY CAST(diff AS UNSIGNED) DESC

Now you will get list like this.

id	name	class	mark	gender	diff
19	Tinny	Nine	18	male	82
17	Tumyu	Six	54	male	46
29	Tess Played	Seven	55	male	45
3	Arnold	Three	55	male	45
6	Alex John	Four	55	male	45
22	Reggid	Seven	55	female	45
10	Big John	Four	55	female	45
4	Krish Star	Four	60	female	40
5	John Mike	Four	60	female	40
20	Jackly	Nine	65	female	35

How to give the option to the user to change the record display order ?

Order by two fields of two different tables

In a discussion forum, members create a Topics ( with title ) and other or same member post reply to this topic.
We have two different tables. One is storing topic with titles and other table is storing topic replies. Each record stores date and time of post. We need to display top 10 recent posts by taking data from title and reply table.
Our order by query should collect data based on recent posts ( it can be topic or reply ) by comparing the posting date and time.

SQL ORDER BY Descending and ascending Command