MySQL ORDER BY and GROUP BY

Whenever you are retrieving information in MySQL, you will probably want to adjust the way it is ordered or grouped. If you plan to output a group of users, you might want the records to be listed in alphabetical order by their name. Other times, you might want to summarize the information like counting the number of people in a particular zip code that are on your mailing list. MySQL makes all of this very easy with two keywords: ORDER BY and GROUP BY. We will first start with creating a tiny generic users table that gives us the name and zip code of our users.

table_users:
Fields: user_id, name, zip_code
Type: int, varchar(100), int
Extra: auto_increment

Now that we have that set up, let's take a look at MySQL's ORDER BY in action:

SELECT *

FROM table_users

ORDER BY name

At a first glance, it looks like a very simple MySQL query. We are simply selecting all of the users' information in the table. However, we see the ORDER BY command that has "name" following it. Essentially, this will order how MySQL outputs the users. In this example, MySQL will output alphabetically because MySQL sees the "name" column as a varchar and defaults to alphabetically order. If our column had been an integer, MySQL would have defaulted to numerical order.

What if we wanted to get the users' names by reverse alphabetical order? All you would have to do is add "DESC" right after the column name like this:

SELECT *

FROM table_users

ORDER BY name DESC

Sometimes we just need to summarize the data in groups. Maybe we want to count the number of people in a zip code, or count the number of users with the same name. Let's take a look at how to use the MySQL GROUP BY keyword:

SELECT zip_code, count(name)

FROM table_users

GROUP BY zip_code

This query would return the number of users in each zip code. As you can see, the MYSQL GROUP BY keyword retrieves summarized information from the database. This is how you can group and order your records using MySQL. Now go forth and apply them to your queries to get the results you want.