SQL ORDER BY DESC

Introduction to SQL ORDER BY DESC

ORDER BY clause in standard query language(SQL) is used to arrange the result set obtained using a SELECT query in an ascending or descending order based on the values of a specified column. ORDER BY clause when used with the DESC keyword, it arranges the result set in descending order based on the value of the specified column.

By default, the ORDER BY statement arranges the data in ascending order. If you wish to sort it in descending order then you must specify the DESC keyword after the column_name based on which sorting has to be done. In this article, we will be discussing the ORDER BY DESC clause in detail with the help of a few examples.

Syntax and Parameters

The basic syntax used for writing the ORDER BY DESC clause is as follows :

SELECT column_name_1, column_name_2
FROM
table_name
ORDER BY column_name_1 DESC;

The parameters used in the above-mentioned syntax are as follows :

• column_name_1, column_name_2, …, column_name_n: Columns or fields that have to be selected for the final result set
• table_name: Table from which the above-mentioned columns or fields have to be fetched.
• Column_name_1: The column or the field according to which the result set has to be sorted in descending order

Here we have used the minimum possible keywords and clauses, but you can consider using ORDER BY clauses in SELECT statements with GROUP BY, HAVING, WHERE clauses, etc. Having discussed the syntax and parameters used for sorting result sets in descending order, let us discuss a few examples.

Examples of SQL ORDER BY DESC

In order to illustrate the usage and functionality of the ORDER BY DESC clause in SQL, what could be better than trying a few examples on a dummy table. Hence, let us create a dummy table called “student_report”. Here is the CREATE TABLE statement for the student_report table.

CREATE TABLE student_report (
id INT NOT NULL,
name character varying(50),
subject character varying(50),
marks numeric NOT NULL
);

The table has been successfully created. Now, let us insert a few records in it to work with. You can use the following insert statement to perform the task.

INSERT INTO public.student_report(
id, name, subject, marks)
VALUES (1,'Raj C','Maths',43.45),
(2,'Samir Sharma','Maths',83.50),
(1,'Raj C','Science',65.25),
(2,'Samir Sharma','English',90),
(3,'Mathew Taylor','English',89),
(3,'Mathew Taylor','Science',84.50),
(1,'Raj C','English',78.25),
(3,'Mathew Taylor','Maths',57),
(2,'Samir Sharma','Science',87);

We are now set to try a few examples based on the ORDER BY DESC statement with the help of the student_report table. SQL queries to illustrate the basic functionality of ORDER BY DESC statement

Example #1

Sort the student_report table in such a manner that the row with the highest marks is at the top.

Code:

SELECT id, name, subject , marks
FROM student_report
ORDER BY marks DESC;

Output:

Example #2

Find the marks obtained by students in Maths and sort the records in descending order by student id.

 Code:

SELECT id, name, subject , marks
FROM student_report
WHERE subject = 'Maths'
ORDER BY id DESC;

Output:

ORDER BY DESC in SELECT statements with GROUP BY clause.

Example #3

Prepare a merit list of students such that the student who has obtained highest marks overall is placed at the top of the list and all other students are listed relatively.

 Code:

SELECT id, name, round((sum(marks)/3),2) as "percentage"
FROM student_report
GROUP BY id, name
ORDER BY percentage DESC;

Output:

Here, we have first computed the percentage of marks obtained by each student using GROUP BY and aggregate functions and then we have arranged the results in descending order using ORDER BY DESC.

Example #4

Find the average marks obtained by the students for each subject and sort the results from highest to lowest by average marks.

SELECT subject, round (AVG(marks), 2) as "average marks"
FROM student_report
GROUP BY subject
ORDER BY AVG(marks) DESC;

Output:

In this example, the average of each subject is calculated by grouping similar subjects together and the final average values are sorted in descending order using ORDER BY with DESC keyword.

ORDER BY DESC with more than one ORDER BY statement.

Example #5

Sort the records of the student_reports table in descending order by student id. If two rows in the ordered set happen to have the same id, sort them in a decreasing manner by marks.

SELECT id, name, subject, marks
FROM student_report
ORDER BY id DESC, marks DESC ;

Output:

Here, the records are first sorted by id in descending order and then by marks again in descending order using ORDER BY DESC. For example, consider the rows with id = 3, since it is the highest id, it’s placed on the top but there are three other rows having the same id. They are sorted again by marks.

Conclusion

In this post, we have covered the ORDER BY DESC clause that is used to sort a given result set in descending order based on the values of a specified column.

Recommended Articles

This is a guide to SQL ORDER BY DESC. Here we also discuss the syntax and parameters of sql order by desc along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. MySQL NOW
2. SQL Virtual Table
3. MySQL Sync
4. SQL Pattern Matching