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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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:
-- Selecting the working_area column, the average commission, and the count of agent names
-- from the AGENTS table
SELECT working_area, AVG(commission), COUNT(agent_name)
-- Filtering the groups to retain only those with fewer than 3 agents
FROM AGENTS
HAVING COUNT(agent_name) < 3
-- Grouping the results by the working_area column
GROUP BY working_area
-- Sorting the results by average commission in ascending order
-- and count of agent names in descending order
ORDER BY AVG(commission), COUNT(agent_name) DESC;
Explanation:
- This SQL code selects the working_area, the average commission, and the count of agent names from the AGENTS table, but only for working areas with fewer than 3 agents.
- The HAVING clause filters the groups to retain only those with fewer than 3 agents.
- The GROUP BY clause groups the results by the working_area column.
- The ORDER BY clause sorts the results first by average commission in ascending order and then by count of agent names in descending order.
- The output will consist of rows showing the working area, the average commission, and the count of agents for each working area where the number of agents is less than 3. The rows will be sorted by average commission in ascending order, and for areas with the same average commission, by count of agents in descending order.
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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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:
-- Selecting the agent_name, working_area, and commission columns
-- from the AGENTS table
SELECT agent_name, working_area, commission
-- Filtering rows to retain only those where commission is less than or equal to 0.13
FROM AGENTS
WHERE commission <= 0.13
-- Sorting the results by the second column (working_area) in descending order
ORDER BY 2 DESC;
Explanation:
- This SQL code selects specific columns from the AGENTS table where the commission is less than or equal to 0.13.
- The SELECT statement retrieves data from the agent_name, working_area, and commission columns.
- The WHERE clause filters the rows to retain only those where the commission column's value is less than or equal to 0.13.
- The ORDER BY clause sorts the results by the second column (working_area) in descending order.
- The output will include the agent name, working area, and commission for agents whose commission is less than or equal to 0.13, sorted by working area in descending order.
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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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:
-- Selecting the working_area column and counting the distinct commission values
-- from the AGENTS table
SELECT working_area, COUNT(DISTINCT commission)
-- Grouping the results by the working_area column
FROM AGENTS
-- Sorting the results by the first column (working_area) in ascending order
-- and the second column (count of distinct commission values) in descending order
GROUP BY working_area
ORDER BY 1, 2 DESC;
Explanation:
- This SQL code counts the distinct commission values for each working area in the AGENTS table.
- The SELECT statement retrieves data from the working_area column and counts the distinct commission values using the COUNT(DISTINCT commission) function.
- The GROUP BY clause groups the results by the working_area column, aggregating the counts for each distinct commission value within each working area.
- The ORDER BY clause sorts the results first by the working_area column in ascending order and then by the count of distinct commission values in descending order.
- The output will consist of rows showing the working area and the count of distinct commission values for each working area, sorted by working area in ascending order and count of distinct commission values in descending order.
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
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics