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 BY keyword always goes after the GROUP BY keyword. 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 data
INSERT INTO Person
VALUES (1,'Sharon Peller','16','Female','Kogi');
INSERT INTO Person
VALUES (2,'Paul Dons','20','Male','Lagos');
INSERT INTO Person
VALUES (3,'Ameera Abedayo','28','Female','Imo');
INSERT INTO Person
VALUES (4,'Maira Elijah','25','Female','Lagos');
INSERT INTO Person
VALUES (5,'David Hassan','30','Male','Abuja');
INSERT INTO Person
VALUES (6,'Niniola Disu','28','Female','Lagos');
INSERT INTO Person
VALUES (7,'Praise Dominion','26','Female','Abuja');
INSERT INTO Person
VALUES (7,'Divine Favour','29','Female','Abuja');
INSERT INTO Person
VALUES (7,'Praise Dominion','31','Female','Lagos');
INSERT INTO Person
VALUES (8,'Joe Smith','16','Male','Lagos');
-- Query 
SELECT name, gender, state
FROM Person
GROUP BY name, gender, state
ORDER BY name, gender;

Explanation

  • Lines 1–7: We create a Person table with the columns—id, name, age, gender, and state.
  • Lines 10–29: We insert data into the Person table.
  • Lines 31–35: We filter data based on some conditions using GROUP BY with ORDER BY.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)