How to combine GROUP BY and ORDER BY keywords in SQL
Overview
In SQL, the GROUP BY with ORDER BY can be used when we want to group our results, compute something about them, and then order them.
SQL GROUP BY and ORDER BY
The GROUP BY keyword is used to group together any rows of a column with the same value, depending on the function defined in the statement.
The ORDER BY keyword sorts columns in ascending or descending order. The default is ascending order.
Note: The
ORDER BYkeyword always goes after theGROUP BYkeyword. We can sort what we’ve not calculated.
Syntax
SELECT column_name(s)
FROM table_name
GROUP BY column_name(s)
ORDER BY column_name(s);
Example
Let’s assume we have a Person table with columns such as name, age, state, and gender.
We want to get the name, gender, and state in the Person table based on gender and state. Let’s order our results by name and gender.
The following code shows how to use the GROUP BY with the ORDER BY keyword in SQL:
CREATE TABLE Person (ID int,name varchar(100),age int,gender varchar(10),state varchar(15));-- Insert dataINSERT INTO PersonVALUES (1,'Sharon Peller','16','Female','Kogi');INSERT INTO PersonVALUES (2,'Paul Dons','20','Male','Lagos');INSERT INTO PersonVALUES (3,'Ameera Abedayo','28','Female','Imo');INSERT INTO PersonVALUES (4,'Maira Elijah','25','Female','Lagos');INSERT INTO PersonVALUES (5,'David Hassan','30','Male','Abuja');INSERT INTO PersonVALUES (6,'Niniola Disu','28','Female','Lagos');INSERT INTO PersonVALUES (7,'Praise Dominion','26','Female','Abuja');INSERT INTO PersonVALUES (7,'Divine Favour','29','Female','Abuja');INSERT INTO PersonVALUES (7,'Praise Dominion','31','Female','Lagos');INSERT INTO PersonVALUES (8,'Joe Smith','16','Male','Lagos');-- QuerySELECT name, gender, stateFROM PersonGROUP BY name, gender, stateORDER BY name, gender;
Explanation
- Lines 1–7: We create a
Persontable with the columns—id,name,age,gender, andstate. - Lines 10–29: We insert data into the
Persontable. - Lines 31–35: We filter data based on some conditions using
GROUP BYwithORDER BY.
