SQL ORDER BY Clause Practice Questions with Solutions

The SQL ORDER BY clause is used to sort query results in either ascending (ASC) or descending (DESC) order. By default, SQL sorts data in ascending order. SQL ORDER BY Clause practice questions with solutions help to understand the concepts.

Sorting data is one of the most common tasks in SQL because users often want to view:

• Highest Marks
• Lowest Marks
• Latest Records
• Employee Salaries
• Product Prices
• Customer Names (A–Z)
• Student Rankings

For example, if you want to display students based on their marks from highest to lowest, you can use:

SELECT *

FROM students

ORDER BY marks DESC;

Without the ORDER BY clause, SQL does not guarantee the order of the returned records.

The ORDER BY clause is widely used in:

• Reports
• Dashboards
• Business Intelligence
• Student Management Systems
• Banking Applications
• HR Systems
• Inventory Software
• E-commerce Websites

What is the SQL ORDER BY Clause?

The ORDER BY clause sorts the result of a query based on one or more columns.

Basic Syntax

SELECT column_name

FROM table_name

ORDER BY column_name;

Ascending Order (Default)

SELECT *

FROM students

ORDER BY marks ASC;

Descending Order

SELECT *

FROM students

ORDER BY marks DESC;

Why Use ORDER BY?

The ORDER BY clause helps you:

• Arrange records alphabetically
• Display highest or lowest values
• Generate rankings
• Sort reports
• Improve readability
• Organize dashboard data

Sample Table Used Throughout This Chapter

students

1. SQL Query to Display Students Sorted by Marks (Ascending)

Problem Statement

Write an SQL query to display students sorted by marks in ascending order.

SQL Solution

SELECT *

FROM students

ORDER BY marks ASC;

Sample Output

Explanation

The ASC keyword sorts records from the smallest value to the largest value.

If ASC is omitted, SQL sorts in ascending order by default.

Concepts Covered

• ORDER BY
• ASC
• Sorting Data

2. SQL Query to Display Students Sorted by Marks (Descending)

Problem Statement

Write an SQL query to display students sorted by marks in descending order.

SQL Solution

SELECT *

FROM students

ORDER BY marks DESC;

Sample Output

Explanation

The DESC keyword sorts records from the largest value to the smallest value.

This is commonly used for:

• Merit Lists
• Salary Reports
• Product Rankings

Concepts Covered

• ORDER BY
• DESC
• Sorting Records

3. SQL Query to Display Students Sorted by Name

Problem Statement

Write an SQL query to display students sorted alphabetically by name.

SQL Solution

SELECT *

FROM students

ORDER BY name ASC;

Sample Output

Explanation

The query sorts student names from A to Z.

This type of sorting is useful for:

• Student Lists
• Customer Directories
• Employee Records

Concepts Covered

• ORDER BY
• Alphabetical Sorting
• ASC

4. SQL Query to Display Students Sorted by Age

Problem Statement

Write an SQL query to display students sorted by age in ascending order.

Sample Table

SQL Solution

SELECT *

FROM students

ORDER BY age ASC;

Sample Output

Explanation

The query arranges students from the youngest to the oldest.

If multiple students have the same age, their relative order depends on the database unless additional sorting columns are specified.

Concepts Covered

• ORDER BY
• ASC
• Numeric Sorting

5. SQL Query to Display Students Sorted by City

Problem Statement

Write an SQL query to display students sorted alphabetically by city.

SQL Solution

SELECT *

FROM students

ORDER BY city ASC;

Sample Output

Explanation

The ORDER BY city ASC clause sorts records alphabetically based on the city column.

This type of sorting is commonly used for:

• Location-wise reports
• Customer lists
• Branch-wise records
• Regional dashboards

Concepts Covered

• ORDER BY
• Text Sorting
• ASC
• Alphabetical Order

6. SQL ORDER BY with WHERE Clause

Problem Statement

Write an SQL query to display Python students, sorted by marks in descending order.

Sample Table

SQL Solution

SELECT *

FROM students

WHERE course = 'Python'

ORDER BY marks DESC;

Sample Output

Explanation

The query first filters students enrolled in the Python course.

Then it sorts the filtered records by marks in descending order.

This type of query is useful for generating department-wise rankings.

Concepts Covered

• WHERE
• ORDER BY
• DESC
• Filtering

7. SQL Query to Display Students Sorted by Age (Descending)

Problem Statement

Write an SQL query to display students sorted by age from highest to lowest.

SQL Solution

SELECT *

FROM students

ORDER BY age DESC;

Sample Output

Explanation

The DESC keyword sorts records from the largest age to the smallest age.

This is useful for age-based reports and seniority analysis.

Concepts Covered

• ORDER BY
• DESC
• Numeric Sorting

8. SQL ORDER BY Multiple Columns

Problem Statement

Write an SQL query to display students sorted by:

1. City (Ascending)
2. Marks (Descending)

SQL Solution

SELECT *

FROM students

ORDER BY city ASC,
         marks DESC;

Sample Output

Explanation

SQL first sorts the records alphabetically by city.

If multiple students belong to the same city, SQL sorts them by marks in descending order.

This technique is commonly used in reports and dashboards.

Concepts Covered

• ORDER BY
• Multiple Columns
• ASC
• DESC

9. SQL Query to Display Students Sorted by Course Name

Problem Statement

Write an SQL query to display students sorted alphabetically by course name.

SQL Solution

SELECT *

FROM students

ORDER BY course ASC;

Sample Output

Explanation

The query arranges all records alphabetically according to the course column.

This type of sorting is useful when preparing course-wise reports.

Concepts Covered

• ORDER BY
• Text Sorting
• ASC

10. SQL ORDER BY with LIMIT

Problem Statement

Write an SQL query to display the top three students based on marks.

SQL Solution

SELECT *

FROM students

ORDER BY marks DESC

LIMIT 3;

Sample Output

Explanation

The query first sorts students by marks in descending order.

Then the LIMIT clause displays only the top three students.

This is commonly used for:

• Leaderboards
• Merit Lists
• Performance Reports

Concepts Covered

• ORDER BY
• LIMIT
• DESC
• Top N Records

11. SQL ORDER BY with DISTINCT

Problem Statement

Write an SQL query to display all unique cities in alphabetical order.

Sample Table

SQL Solution

SELECT DISTINCT city

FROM students

ORDER BY city ASC;

Sample Output

Explanation

The DISTINCT keyword removes duplicate city names.

The ORDER BY clause then sorts the remaining cities alphabetically.

This query is commonly used for generating location lists in reports.

Concepts Covered

• DISTINCT
• ORDER BY
• ASC
• Unique Records

12. SQL ORDER BY Multiple Numeric Columns

Problem Statement

Write an SQL query to display students sorted by:

1. Marks (Descending)
2. Age (Ascending)

SQL Solution

SELECT *

FROM students

ORDER BY marks DESC,
         age ASC;

Sample Output

Explanation

SQL first sorts the records by marks in descending order.

If two students have the same marks, SQL sorts them by age in ascending order.

This type of sorting is useful for ranking systems.

Concepts Covered

• ORDER BY
• Multiple Columns
• DESC
• ASC

13. SQL Query to Display Students Sorted by Name (Descending)

Problem Statement

Write an SQL query to display students sorted by name in reverse alphabetical order.

SQL Solution

SELECT *

FROM students

ORDER BY name DESC;

Sample Output

Explanation

The DESC keyword sorts names from Z to A.

This sorting is useful for reverse alphabetical listings and reports.

Concepts Covered

• ORDER BY
• DESC
• Alphabetical Sorting

14. SQL ORDER BY with WHERE and LIMIT

Problem Statement

Write an SQL query to display the top two Java students based on marks.

SQL Solution

SELECT *

FROM students

WHERE course = 'Java'

ORDER BY marks DESC

LIMIT 2;

Sample Output

Explanation

The query performs three operations:

1. Filters Java students.
2. Sorts them by marks in descending order.
3. Displays only the top two students.

This approach is commonly used in leaderboards and department-wise reports.

Concepts Covered

• WHERE
• ORDER BY
• LIMIT
• DESC

15. Real-World SQL ORDER BY Example

Problem Statement

A school wants to generate a merit list of students sorted by highest marks.

Write an SQL query to generate the report.

SQL Solution

SELECT id,
       name,
       course,
       marks

FROM students

ORDER BY marks DESC;

Sample Output

Explanation

This query creates a merit list by arranging students from the highest scorer to the lowest scorer.

Real-world applications include:

• School Merit Lists
• Employee Performance Reports
• Product Rankings
• Sales Reports
• Customer Leaderboards
• Dashboard Analytics

Concepts Covered

• ORDER BY
• DESC
• Ranking
• Reporting

Chapter Summary

In this chapter, you learned how to use the SQL ORDER BY clause to sort query results in ascending (ASC) and descending (DESC) order. Sorting data is an essential SQL skill because most real-world applications display information in an organized format, such as highest marks, latest records, or alphabetical lists.

You also explored how ORDER BY can be combined with other SQL clauses to create more practical and powerful queries.

Throughout this chapter, you covered:

• Introduction to the ORDER BY clause
• Ascending Order (ASC)
• Descending Order (DESC)
• Sorting by Names
• Sorting by Numbers
• Sorting by Cities
• Sorting by Multiple Columns
• ORDER BY with WHERE
• ORDER BY with LIMIT
• ORDER BY with DISTINCT
• Real-world Sorting Examples

The ORDER BY clause is widely used in reporting, dashboards, search results, and business analytics.

Key Takeaways

• ORDER BY sorts query results.
• ASC sorts from smallest to largest or A to Z.
• DESC sorts from largest to smallest or Z to A.
• ASC is the default sorting order.
• Multiple columns can be used in a single ORDER BY.
• ORDER BY is commonly combined with WHERE.
• ORDER BY works well with LIMIT for Top N queries.
• ORDER BY improves report readability.
• Sorting is widely used in dashboards and analytics.
• ORDER BY is one of the most frequently asked SQL interview topics.

Frequently Asked Questions (FAQs)

1. What is the SQL ORDER BY clause?

The ORDER BY clause sorts the records returned by a query.

Example:

SELECT *

FROM students

ORDER BY marks DESC;

2. What is the default sorting order?

The default sorting order is Ascending (ASC).

Example:

SELECT *

FROM students

ORDER BY marks;

This is equivalent to:

SELECT *

FROM students

ORDER BY marks ASC;

3. What is the difference between ASC and DESC?

ASC

• Smallest to Largest
• A to Z

DESC

• Largest to Smallest
• Z to A

Example:

SELECT *

FROM students

ORDER BY name DESC;

4. Can ORDER BY sort multiple columns?

Yes.

Example:

SELECT *

FROM students

ORDER BY city ASC,
         marks DESC;

SQL sorts by city first.

If two records have the same city, they are sorted by marks.

5. Can ORDER BY be used with WHERE?

Yes.

Example:

SELECT *

FROM students

WHERE course = 'Python'

ORDER BY marks DESC;

SQL first filters the records and then sorts them.

6. Can ORDER BY be used with LIMIT?

Yes.

Example:

SELECT *

FROM students

ORDER BY marks DESC

LIMIT 5;

This displays the top five students.

7. Where is ORDER BY used in real-world applications?

The ORDER BY clause is commonly used in:

• Student Result Systems
• Banking Applications
• Hospital Management
• Employee Reports
• Inventory Systems
• CRM Software
• Sales Dashboards
• Product Listings
• Business Intelligence Reports
• E-commerce Websites

8. Can ORDER BY sort text values?

Yes.

Example:

SELECT *

FROM students

ORDER BY name ASC;

SQL sorts text alphabetically according to the database collation.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar