Jamie Balfour

Welcome to my personal website.

Find out more about me, my personal projects, reviews, courses and much more here.

Part 5.2Ordering and grouping results in SQL

Part 5.2Ordering and grouping results in SQL

A database is designed to be an efficient means of accessing information as well as an organised way. MySQL offers ways to organise data prior to returning the result table.

Ordering

Ordering allows data to be returned sorted. This means that an application written in a language such as PHP or JSP will not need to sort the data since it was sorted by the MySQL system.

Data can be sorted on a specific field. If the data is not sorted, it will be returned in a way that the relational database management system sees best to sort the data when storing it.

There are two modes of ordering, sorting by ascending order and sorting by descending order.

The following table will be used again with the samples:

Attendees

forename surname dob
Jane Doe 1991-12-01
Adam Daniels 1989-01-13
Frank Roosevelt 1992-04-19
Jane French 1995-02-28

The SQL syntax which will sort a table by the surname field is as follows:

SQL
SELECT * FROM `Attendees` ORDER BY `surname`
		

The resulting table would be:

forename surname dob
Adam Daniels 1989-01-13
Jane Doe 1991-12-01
Jane French 1995-02-28
Frank Roosevelt 1992-04-19

Grouping

MySQL also allows for grouping of tuples. Grouping can be used in conjunction with a sort since a grouping can be made on one field and a sort on another.

The following demonstrates a grouping on the table above:

SQL
SELECT * FROM `Attendees` GROUP BY `surname`
		

The results table is as below. It is identical to the ORDER BY clause example above.

forename surname dob
Adam Daniels 1989-01-13
Jane Doe 1991-12-01
Jane French 1995-02-28
Frank Roosevelt 1992-04-19

However, using the same tables, grouping can be used to group everyone with the name 'Jane' and find the number of people called 'Jane'. This takes advantage of the SQL aggregate COUNT function:

SQL
SELECT `forename`, COUNT(*) FROM `Attendees` GROUP BY `forename`
		
forename COUNT(*)
Adam 1
Frank 1
Jane 2

Having

One of the issues with the WHERE clause is that it does not work on aggregate functions. This means it is not possible to check the count of items in a group. In the example above, all rows were returned, but what if only the rows where the name occurs twice was to be shown. This would be more useful if looking for a list of all people called Jane who appear more than once. An example of this is shown below:

SQL
SELECT `forename`, COUNT(*) FROM `Attendees` GROUP BY `forename` HAVING COUNT(*) > 1
		

This limits the result to:

forename COUNT(*)
Jane 2
Feedback 👍
Comments are sent via email to me.