As always, I am sharing what I learn here on my blog. As I continue to study SQL, the language never ceases to amaze me with those little intricacies you don’t necessarily learn of unless you are bitten by them, or through intentional study. Luckily, my case is the latter of the 2. I’m referring to the ORDER BY clause when used in GROUP BY queries. Continue reading for more information.
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
Focused SQL Study
Studying out of the book OCA Oracle Database SQL Exam Guide (not an affiliate link), I read some information that ORDER BY is somewhat limited when a GROUP BY clause is present. I feel like I should already know this. But, to solidify my learning, I am writing about it here.
I’m using the Oracle database as that is the implementation I am focused on learning more about. I am not 100% sure if these principles apply to other SQL vendors.
Sample Data
For the example queries, I’m using the EMPLOYEES table that is part of the popular practice HR schema:
Order By a column present or not present in the SELECT clause list
This is a relatively simple query with some DEPARTMENT_ID, JOB_ID, and SALARY information. Notice I ORDER BY the DEPARTMENT_ID, which is present in the SELECT clause list:
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID;
(Note: Some output rows constrained to what is available in the given screenshot.)
This next query is similar to the previous one above. The difference is the DEPARTMENT_ID is not included in the SELECT clause list. Yet, I can ORDER BY the DEPARTMENT_ID column with no issues:
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID;
(Note: Some output rows constrained to what is available in the given screenshot.)
Consider making a donation as I continue to provide valuable content here on my blog. Thank you so much!!! Every bit is much appreciated and helps tremendously!
Introduce ORDER BY clause in a GROUP BY query
Now let’s execute a query and SUM() the SALARY column. We must GROUP BY any columns that are not part of an aggregate function call. In this case, the JOB_ID column. However, look at what happens when I try and ORDER BY the DEPARTMENT_ID column:
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY DEPARTMENT_ID;
Turns out, that query is not allowed.
Why not? In some of the previous example queries, I did ORDER BY a column whether the said column was in the SELECT clause list or not.
ORDER BY limitations in a GROUP BY query
Because ORDER BY is limited when a GROUP BY clause is used. Let’s visit what exactly is allowed in an ORDER BY clause in this specific context.
ORDER BY a column that is part of the SELECT clause list or is named in the GROUP BY clause
You are able to ORDER BY a column that is present in the SELECT clause list or is named in the GROUP BY clause. In the following query, I use the JOB_ID column in the ORDER BY clause, which covers both specifications (termed loosely here) in this case:
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;
(Related: ORDER BY targets can be specified by either name, alias, or numerical column list position.)
ORDER BY an aggregate function
For this example, I order by the SUM(SALARY) aggregate function call, which is perfectly valid:
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY SUM(SALARY)
Interested in a weekly email from me about what I’m doing in the SQL/PHP world? Visit this sign-up page and subscribe. I promise no spam. Thank you!
ORDER BY any Aggregate function
What seems even trickier to me, the sorting aggregate function need not be present in the SELECT clause. Here I use the MAX() aggregate function on the HIRE_DATE column:
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY MAX(HIRE_DATE);
Below is a follow-up query with the MAX(HIRE_DATE) aggregate function call in the SELECT clause list for a reference of the sorting order:
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY MAX(HIRE_DATE);
Not Covered: USER, SYSDATE, UID
The book of reference also mentions that ORDER BY can specify USER, SYSDATE, or UID when a GROUP BY Is present. At the current time of writing, I am not covering this functionality as I am unfamiliar with the best context for these specifics to be used.
If you see anything incorrect or have questions about the code, please leave a comment in the comments section below. Thank you for reading!
Please help make this blog post better by adding any comments highlighting your experience with ORDER BY sorting for queries using the GROUP BY clause.
Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.