SQL ORDER BY Descending and ascending Command
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 descSELECT * 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 |
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 classSELECT * 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 descOrder 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 , nameSo 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 DESCSELECT * FROM student ORDER BY mark DESC, name DESCHow 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 SELECT * FROM `student` order by diff desc| 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 |
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| 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.
SELECT dt , topic_id,userid
FROM (
SELECT topic_id, rdtp AS dt, userid
FROM forum_reply
UNION ALL
SELECT topic_id, tdtp AS dt, userid
FROM forum_topics
) t
ORDER BY dt DESC limit 0,10Selecting records SQL LEFT Join to link tables
Subscribe to our YouTube Channel here
This article is written by plus2net.com team.
https://www.plus2net.com
| jigger | 11-06-2013 |
| just want to asked guys, hope you help me this is the scenario i have at least 3 data in my database from ID 1, 2, 3, and i want to display this file or post, data display is OK but i want to display like this 3, 2, 1 how can make it that way. | |
| annu | 10-10-2014 |
| Suppose if we are using 1st, 2nd and 3rd in class instead of two three and four how will this query will work in that case | |
| Steve Highley | 27-10-2014 |
| The desc qualifier (which stands for descending, i.e. high to low) changes the sequence from the default of low to high. Data is ordered depending on the data type. Text is ordered according to collating sequence, numbers from low to high (e.g. -100 is before 5), and dates are ordered from earliest to latest. So 'Three' is greater than 'Four' because T is after F in the collating sequence. But 3 is less than 4 whether stored as numbers or text. I hope that helps. | |
| yamsoti | 26-03-2016 |
| I want to display 2 highest mark from class 4, it has marks 70, 60, 60, 50. How do I prepare the query. | |
| smo1234 | 07-04-2016 |
| Your can read how to get second highest by using order by and limit | |
SQL Video Tutorials