This lessons explains the keywords distinct, order by and count. If you want to order by count or order by sum, go to the chapter Order By Count.
The purpose of DISTINCT is to prevent the same information to be displayed more than once.
Example: we want to show the cities in which the company had revenues. If we use the following statement, some cities will appear twice in the results :
Indeed, there can be one line for a city corresponding to revenues from year 2013, and another with the same city corresponding to revenues from year 2014.
In order to see each city only once, we use the following syntax:
Note that the distinct keyword can be followed by more than one column. In that case, the syntax is the following:
You write the distinct keyword only once, on the left hand side of the first column.
With such a query:
- If two clients have the same first name but a different last name, they will both appear in the results
- If two clients have the same last name but a different first name, they will both appear in the results
- If two clients have the same first name and last name (it is rare, but it can happen), this combination of first name and last name will appear only once.
The purpose of SQL COUNT(*) is to count the number of lines returned by the SQL query.
To count the number of rows in the revenues table, we use the following statement:
You can put the keyword distinct followed by a column name inside the parenthesis instead of a star. In that case, you will get the number of distinct values present in this column.
With the following query, you can get the number of cities in the table revenues:
SQL ORDER BY
The purpose of the SQL ORDER BY statement is to order the lines appearing in the results.
Example: in the results, you want the customer last name to appear in alphabetical order, from a to z (the letter “a” appearing on the top of the result, “z” on the bottom). You would use the following syntax:
Note that “asc” is the default way of ordering, so writing “asc” is optional.
If you want the name to appear from z to a (“z” appearing on the top of the result, “a” on the bottom), you would use “desc” instead of “asc”:
You may have clients that have the same last name. In that case, you can order the results by last name, and then for each last name order the results by first name:
When using order by with number:
- order by … asc would show the highest numbers on the bottom of the results.
- order by … desc would show the highest numbers on the top of the results.
Order by Count
You can use the result of a count to order your results. Example: you want to show the states where your company have clients, showing the states with the most clients on the top.
The syntax is the following:
Notice that a group by was necessary to calculate the count, and that you used the count(*) keyword just after order by. Using the same technique, you could order by sum:
In this example, we just ordered the state by the total revenue of sales transactions per state.