MySQL Group By Having Limit Offset and More!

MySQL Group By Having Limit Offset

We’re moving onward in our MySQL Journey! This episode will take a look at many useful features of the language and how you can use them. As always, we’ll simply be operating on the data that we already have in the friends table we’ve been working with so far. We’ll cover features like group by and aggregate functions. They typically go together, much like peanut butter and jelly. In addition to this, we’ll look at things such as group_concat, having, and order by. Sort orders are important as well so we will look at sorting in ascending order as well as descending order. Lastly, testing out queries using the limit, offset, and functions with order by will be reviewed. Let’s do it!


What is Group By in MySQL?

We can use the group by statement in combination with aggregate functions in MySQL to group the results by a specific column. An example would be to find all of our friends that have the same last name. We’ll need to use both the group by statement and the count aggregate function to make this happen. Let’s see how:

Nice Job. Here we can see that there are three couples in our group of friends. The first column tells us the last name, and the second column gives us the number of times that last name occurs. Quite useful.

There are many ways to apply the group by statement when using aggregate functions like count, avg, min, max, and sum. You’ll need to have a play with all of these on various tables to see how you can combine them to get the results you are looking for. Let’s try a query to find the youngest of our friends and group them by their last name.

This query grabs the youngest of our friends and groups them by last name. There are a few things to note here. The last_name column came back in alphabetical order, but there are only seven records when we know we have ten friends, why is that? Well in this case, it almost works like a distinct statement like we see here:

group by can be a little tricky at first but once you try a few tests of your own, you’ll find it to come easier. Be aware that the field which you use the group by on, needs to also be part of the select statement. Additionally, the group by is applied to the field you want information on, not the field which gets the aggregate function applied to it. The example above was looking for information on the last_name field but the aggregate function was applied to the birthday field.

Group Concat

This is a cool little function we can make use of when working with the group by statement. This function allows us to stick together, or concatenate, things in a group. Let’s find the cell phone numbers of the couples in our friends table.

Notice how the cell phone numbers for couples are now concatenated together in the result set. Now by default, the group_concat function uses a comma to separate the items being concatenated. If you would like to use a different delimiter, you can so so like this:

Now we are simply using a pipe character instead of the comma. Follow the syntax in the example above and simply swap out the values you would like to use in your test environment.

Having

You may have seen this particular keyword in various MySQL syntax you have come across. So what does the having keyword do? Well, it is used as a type of filter for aggregates or groups of results. Therefore, it fits right in with the group by clause that we have been testing out so far. Let’s apply a having clause to our query and see what result it gives us.

So this is pretty cool. What happens is by using the having clause in our example, we are telling MySQL to get us all the results, but filter them down to only those that have two. So since there are two Farmingtons, two Olivios, and two Penblancs, they they are the ones who get returned in our results.

How to use Order By in MySQL

This is one of the more common ones you’ll come across. This clause let’s us, you guessed it, order are results by a specific column. We might as well get right down to testing this out!

The above example has two queries to talk about. In the first query we order by the last_name column. In the second query, we order by the id of the house or house_id column. Note that by default, MySQL provides results in ascending order. We can change this by adding the desc clause like so:

Bingo Bango! Works like a charm! When using order by, we can provide more than one column to sort on. Let’s sort our friends by house_id and first_name.

The reason why we did two queries is so we could illustrate the difference between using one or two columns when sorting. Now, we provide the house_id as the first option to the order by clause. The first field takes precedence! Notice that in both cases, whether we provide one or two columns to sort on, the house_id column runs from 1 to 7 in order. The way the second sort works is to look at the results of the first sort, and then apply a sub sort to the results. This is why you’ll notice that the order of Jacob and Mary is different between the two queries – even though they both live in house_id 7! Dual sorts can be a little confusing, so just be sure to practice several different use cases to get the concept down solid.

Putting MySQL Limit to Good Use

The limit clause is very useful to reduce the number of results that may be returned from a query. Maybe you don’t want all the results, only the first two or three for various reasons. Let’s find just the oldest person among our friends, and return only that one result.

It looks like Jack is our oldest friend, but he still has a lot of good years left in him! 50 is the new 20 after all. What if we wanted to get only the second oldest, how could we do that? Actually, let’s try a couple of queries. First we’ll find the second oldest, then we’ll find the third and fourth oldest together. This will provide a nice illustration of how to get the results you want by using limit and offset together:

Bazinga! This is exactly what we were looking for. In the first query, we have a limit of 1. This says to take only 1 result. The offset is set to 1 as well. What this says, is that instead of starting to grab records on the first result, start grabbing at the second. Think zero based here, just like arrays in other programming languages. In the second query, we have a limit of 2. This says to fetch exactly 2 records. The offset is set to 2 in this case as well. Using zero based math, this says to start at the third record. This gives us the third and fourth oldest friends, just like we were looking for.

Using a Function in the Order By

We can do even more cool things with the order by clause by combining it with MySQL functions. Let’s order our friends by the numeric day on which they were born:

Jacob was born on the first, Sofia on the tenth, Mary on the eleventh, Sheila on the twelfth, Emmet on the fifteenth, Brenda on the fifteenth, Frankie on the seventeenth, Jack on the twenty third, Dave on the twenty fourth, and Jim on the twenty fourth. Pretty Slick!

Conclusion

There were a lot of different approaches to constructing queries that we covered in this episode. Whether we were testing out aggregate functions in use with the group by clause, or selecting data within a specific range using limit and offset – we sure had a lot of fun practicing our MySQL skills. As always, keep practicing these and others on your own databases and get your skills sharpened.