w3resource

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 Expression: SQL ordering output using more than one column number.

Relational Algebra Tree:

Relational Algebra Tree: SQL ordering output using more than one column number.

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.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

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



Follow us on Facebook and Twitter for latest update.