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
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