Both GROUP BY and ORDER BY are clauses (or statements) that serve similar functions; that is to sort query results. However, each of these serve very different purposes; so different in fact, that they can be employed separately or together. And that is where things can get a little dicey if you are unsure of what you're doing. In today's blog, we'll learn what each clause does and how to use them together for the ultimate control over your query output. To do that we'll be using Navicat Premium against the Sakila Sample Database.
GROUP BY and ORDER BY Explained
The purpose of the ORDER BY clause is to sort the query result by one or more columns. Meanwhile, the GROUP BY clause is used to arrange data into groups with the help of aggregate functions such as COUNT(), AVG, MIN() and MAX(). The way that it works is, if a particular column has the same values in different rows then it will amalgamate these rows into a group.
Let's look at an example of each.
Here's a query that displays the first and last names of all actors from the table actor, sorted by last name, followed by first name:
Now, here's another query that groups actors by the number of films that they have appeared in:
Using Group By and Order By Together
Notice that, in the preceding query, records are ordered by the actor_id field, which is what results are grouped on. If we wanted to order results using different - i.e. non-grouped - fields, we would have to add an ORDER BY clause. Here's the same query, but ordered by the number of films which each actor has appeared in, from most to least:
Notice that, once you include the Order By clause, the default group ordering is lost. If you'd like to keep it, you can add grouped columns to the Order By field list:
Points to Keep in Mind
When combining the Group By and Order By clauses, it is important to bear in mind that, in terms of placement within a SELECT statement:
- The GROUP BY clause is placed after the WHERE clause.
- The GROUP BY clause is placed before the ORDER BY clause.
GROUP BY goes before the ORDER BY statement because the latter operates on the final result of the query.
Bonus Section: the Having Clause
You can filter the grouped data further by using the HAVING clause. The HAVING clause is similar to the WHERE clause, but operates on groups of rows rather than on individual rows. To illustrate how the HAVING clause works, we can use it to limit results to those actors who've appeared in more than ten films:
Navicat's SQL Editor greatly facilitates query writing thanks to features like syntax highlighting, reusable code snippets for control flow/DDL/syntax statements, as well as auto-complete. It can suggest everything from schema, tables, and columns to stored procedure and functions. Here is the HAVING keyword:
The Having Clause should be placed after the Group By clause, but before the Order By clause.
Conclusion
In today's blog, we learned what each clause does and how to use them together for the ultimate control over your query output using Navicat Premium.
Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!
Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial organizations. You can hire Rob by emailing him at rgconsulting(AT)robgravelle(DOT)com. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.
