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:
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:
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:
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:
SELECT `forename`, COUNT(*) FROM `Attendees` GROUP BY `forename` HAVING COUNT(*) > 1
This limits the result to:
| forename | COUNT(*) |
|---|---|
| Jane | 2 |
