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:

id username password birthday
1 bobdole32 secretP 1984-06-01
2 rustyMeerkat digholes 1995-09-15
3 rustyMeerkat copyCat 1995-09-15
4 bobdole32 secretP 1984-06-01
5 rustyMeerkat digholes 1995-09-15
6 bobdole32 copied 1984-06-01
Example

Result
id username password birthday
1 bobdole32 secretP 1984-06-01
4 bobdole32 secretP 1984-06-01
6 bobdole32 copied 1984-06-01
2 rustyMeerkat digholes 1995-09-15
3 rustyMeerkat copyCat 1995-09-15
5 rustyMeerkat digholes 1995-09-15

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

Example

Result
id username password birthday
1 bobdole32 secretP 1984-06-01
2 rustyMeerkat digholes 1995-09-15

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

Example

Result
username
bobdole32
rustyMeerkat

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.

If you enjoyed this resource, support me by sharing this page with others.
Share on Facebook
Facebook
Share on Google+
Google+
Share on StumbleUpon
StumbleUpon
Digg this
Digg
Tweet about this on Twitter
Twitter
Share on Reddit
Reddit

Leave a Comment

  1. Mentioned GROUP BY behaviour for nonaggregated columns in SELECT clause is not common.
    In standard SQL nonaggregated columns in SELECT list are illegal for query that includes a GROUP BY clause.
    I believe, it could be MySQL extension to GROUP BY; in this case it may have sense to emphasize specific DBMS where such construction will work.
    Nevertheless, thanks for your work on this course!

  2. on GROUP BY SQL Keyword

    per Example

    SELECT *
    FROM table_name
    GROUP BY username

    Result

    id username password birthday
    1 bobdole32 secretP 1984-06-01
    2 rustyMeerkat digholes 1995-09-15

    i need the Result like this:

    id username password birthday
    6 bobdole32 copied 1984-06-01
    2 rustyMeerkat digholes 1995-09-15

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.