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*FROMSTUDENTORDER BYMARK;
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 BYmarkDESC;
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.
Here is the command to display the records in the order of class
SELECT * FROM`student`ORDER BYclass
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 BYclassDESC
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 * FROMstudentORDER BYmark, 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 * FROMstudentORDER BYmark, nameDESC
If you want listing should be from highest mark to lowest mark then query should be
SELECT * FROMstudentORDER BYmarkDESC, nameDESC
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
UPDATEstudentSETdiff = 100 - mark
Now use order by query.
SELECT * FROM`student`ORDER BYdiffDESC
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
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.