ORDER BY and GROUP BY

Now, that we can extract data like little masterminds of SQL, but what good is getting data if we can’t organize or understand it? SQL’s ORDER BY, GROUP BY, and DISTINCT are a few of the cornerstones in SQL for managing and organizing the data received from the database. ORDER BY handles the order the records will be returned. GROUP BY finds similar results and clumps them together. DISTINCT prevents the return of duplicate data. Getting excited? Me too! Example time:

ORDER BY SQL Keyword

We’ll just use one big table for all of these:

SELECT * 
FROM table_name
ORDER BY username

Ah, the power of ORDER BY still amazes me. Before we used the ORDER BY, it was just going to return all of the records with every column. By adding ORDER BY and setting it equal to username, SQL looks at the value type, which it sees that it is a varchar(data type in SQL to represent short strings). Since it is alphabetical, SQL will order them from a-z. That’s why ‘bobdole32’ appears first. Now, what if we wanted it to return from z-a? Two more important keywords are commonly used with ORDER BY, they are DESC for descending and ASC for ascending. SQL defaults with ascending order. You would just implement it like so: ORDER BY username DESC.

GROUP BY SQL Keyword

SELECT * 
FROM table_name
GROUP BY username

Whew! Finally, we have more manageable results. If you look closely, you will see we lost a considerable amount of data in the process. We now only have 2 requires because we only had 2 different usernames. Now if I remember correctly, I don’t believe GROUP BY is as predictable as it looks in this example. I think that I heard somewhere that in those 2 groups of similar usernames, it actually selects a random record to display rather than simply choosing the first one. Typically, GROUP is used with functions. For instance, it might be used with a SUM() function to create one row that would tally up the age of all ‘rustyMeerkat’ usernames. Of course, we’ll get to that later. Basically, you just need to understand that using GROUP BY does what it says, it groups by the column name you give it.

DISTINCT SQL Keyword

SELECT DISTINCT username
FROM table_name

DISTINCT is slightly awkward in it’s position, but I’ll explain why it must be there. DISTINCT is intended to be used directly after declaring the statement as a SELECT. This permits SQL to see what columns must be unique in order to show the results. If you were to put in multiple column names like, SELECT DISTINCT username, password, you would see 4 records. It is because it looks at all of the columns you specify and determines if in any given records are the same as another record. Since we added more columns, you are less likely to have matches, which means SQL will return more records. Pfft. That wasn’t too bad.