SQL order by with more columns

In this page, we are going to discuss, how the SQL ORDER BY clause can be used to impose an order on the result of a query.

SQL order by with more columns using aggregate function

Sample table: agents

To get the columns 'working_area', average 'commission' and number of agents for each group of 'working_area' from the 'agents' table with the following condition -

1. number of agents for each group of 'working_area' must be less than 3,

the following SQL statement can be used:

SQL Code:

SELECT working_area, AVG(commission),COUNT(agent_name) 
FROM AGENTS
HAVING COUNT(agent_name)<3
GROUP BY working_area
ORDER BY AVG(commission),COUNT(agent_name)DESC;

Output:

WORKING_AREA                        AVG(COMMISSION) COUNT(AGENT_NAME)
----------------------------------- --------------- -----------------
Hampshair                                       .11                 1
Mumbai                                          .11                 1
New York                                        .12                 1
San Jose                                        .12                 1
Brisban                                         .13                 1
London                                          .14                 2
Chennai                                         .14                 1
Torento                                         .15                 1

SQL ordering output by column number

In the following, we are going to discuss, how an index number for a column can be used to make the result of a query in descending order based on that column.

The SQL ORDER BY clause is used to impose an order on the result of a query. The ORDER BY can be imposed on more than one columns and a column index number can also be mentioned instead of column name.

Example:

Sample table: agents

To get the columns 'agent_name', 'working_area' and 'commission' with an arranged order on column number 2 i.e. 'working_area' column, from the mentioned column list from the 'agents' table with the following condition -

1. 'commission' of 'agents' table must be less than or equal to .13,

the following SQL statement can be used:

SQL Code:

SELECT agent_name, working_area, commission
FROM AGENTS
WHERE commission<=.13
ORDER BY 2 DESC;

Output:

AGENT_NAME                               WORKING_AREA                        COMMISSION
---------------------------------------- ----------------------------------- ----------
Lucida                                   San Jose                                   .12
Alford                                   New York                                   .12
Mukesh                                   Mumbai                                     .11
Alex                                     London                                     .13
Benjamin                                 Hampshair                                  .11
Anderson                                 Brisban                                    .13

SQL ordering output using more than one column number

In the following, we are going to discuss, how more than one index numbers for one or more columns can be used to make the result of a query in descending order based on those columns.

Example:

Sample table: agents

To get the column 'working_area' and number of unique 'commission' for each group of 'working_area' named as 'count(distinct commission)' from the 'agents' table by an arranged order on column index number 1 and 2 i.e. 'working_area' and number of unique 'commission' for each group of 'working_area', the following SQL statement can be used :

SQL Code:

SELECT working_area, COUNT(DISTINCT commission)
FROM AGENTS
GROUP BY working_area
ORDER BY 1,2 DESC;

Relational Algebra Expression:

Relational Algebra Tree:

Output:

WORKING_AREA                        COUNT(DISTINCTCOMMISSION)
----------------------------------- -------------------------
Bangalore                                                   2
Brisban                                                     1
Chennai                                                     1
Hampshair                                                   1
London                                                      2
Mumbai                                                      1
New York                                                    1
San Jose                                                    1
Torento                                                     1

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: Putting text in query output
Next: SQL ordering output by column number with group by