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.
SELECT * FROM STUDENT ORDER BY MARK
idnameclassmark sex
19TinnyNine18male
17TumyuSix54male
29Tess PlayedSeven55male
3ArnoldThree55male
6Alex JohnFour55male
22ReggidSeven55female
10Big JohnFour55female
4Krish StarFour60female
5John MikeFour60female
20JacklyNine65female

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 decending 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
idnameclass mark
2Max RuinThree85
3ArnoldThree55
1John DeoFour75
4Krish StarFour60
5John MikeFour60
6Alex JohnFour55

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
1John DeoFour75
4Krish StarFour60
5John MikeFour60
6Alex John Four55
2Max RuinThree85
3ArnoldThree55

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.
idnameclassmarksexdiff
19TinnyNine18male82
12ReckySix94female6
17TumyuSix54male46
22ReggidSeven55female45
29Tess PlayedSeven55male45
3ArnoldThree55male45
6Alex JohnFour55male45
10Big JohnFour55female45
4Krish StarFour60female40
5John MikeFour60female40
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.
idnameclassmarksex diff
19TinnyNine18male82
17TumyuSix54male46
29Tess PlayedSeven55male45
3ArnoldThree55male45
6Alex JohnFour55male45
22ReggidSeven55female45
10Big JohnFour55female45
4Krish StarFour60female40
5John MikeFour60female40
20JacklyNine65female35


How to give the option to the user to change the record display order ?
Visitors Rating
Your Rating




Google+
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.
subhendu

07-04-2016

Your can read how to get second highest by using order by and limit

Post Comment This is for short comments only. Use the forum for more discussions.








HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2018 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer