Home » Tutorials » SQL » ORDER BY and GROUP BY

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:

idusernamepasswordbirthday
1bobdole32secretP1984-06-01
2rustyMeerkatdigholes1995-09-15
3rustyMeerkatcopyCat1995-09-15
4bobdole32secretP1984-06-01
5rustyMeerkatdigholes1995-09-15
6bobdole32copied1984-06-01
Example
SELECT * 
FROM table_name
ORDER BY username
Result
idusernamepasswordbirthday
1bobdole32secretP1984-06-01
4bobdole32secretP1984-06-01
6bobdole32copied1984-06-01
2rustyMeerkatdigholes1995-09-15
3rustyMeerkatcopyCat1995-09-15
5rustyMeerkatdigholes1995-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
SELECT * 
FROM table_name
GROUP BY username
Result
idusernamepasswordbirthday
1bobdole32secretP1984-06-01
2rustyMeerkatdigholes1995-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
SELECT DISTINCT username
FROM table_name
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.



Link/cite this page

If you use any of the content on this page in your own work, please use the code below to cite this page as the source of the content.

  • Stewart, Suzy. "ORDER BY and GROUP BY". After Hours Programming. Accessed on March 16, 2024. https://www.afterhoursprogramming.com/tutorial/sql/order-by-and-group-by/.

  • Stewart, Suzy. "ORDER BY and GROUP BY". After Hours Programming, https://www.afterhoursprogramming.com/tutorial/sql/order-by-and-group-by/. Accessed 16 March, 2024.

  • Stewart, Suzy. ORDER BY and GROUP BY. After Hours Programming. Retrieved from https://www.afterhoursprogramming.com/tutorial/sql/order-by-and-group-by/.



2 thoughts on “ORDER BY and GROUP BY”

  1. 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

  2. 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!

Leave a Comment

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