Preface: My SQL Server Management Studio took a nosedive, so I will not be including image-based examples from SSMS in the Tutorials until I resolve the issue... but alas, the show must go on.
Hey Everyone,
It's that time again!! I think Mabel (from Cartoon Network's Gravity Falls) has hit the nail on the head! There you see her sorting her fellow groupies and doing a FABULOUS job of it. Poor boys in the corner were segregated out of her group though... maybe next time.
So today's lessons are going to be about grouping and sorting results. This basically means that I'm going to teach you how to query for data that returns in such a way as to be grouped together based on one or more specific fields. Additionally, you're going to learn how to sort the data forwards and backwards by one or more fields. You'll become the Master of the Sorting Hat!!
The Sorting Hat from Harry Potter
Apples & Bananas
Grouping data is like bagging up apples and bananas, each segregated to its own fruit... yeah, it's a Fruitist Mentality that needs to be abolished! So I just ate them all anyway!!
In the great big world of Databases and Queries, one of the most useful tools is the GROUP BY clause. This SQL clause allows the returning dataset (this is another word for the query results that are returned to you after the query has completed) to be grouped by specific fields so that you can separate the data items accordingly and create more efficient analytical reports (and other things as well).
Above you can see the following 2 different queries:
SELECT * FROM BackPack
GROUP BY color, id, backpack_id,length, width, height, weight, strap_length
SELECT * FROM BackPack
GROUP BY backpack_id, color, id, length, width, height, weight, strap_length
If you look at the 2 Result Sets in the above image, you will see that they return rows in a different order. This is due to the GROUP BY clause that was included in the queries. The first query does a GROUP BY against the color field and then the id field before it looks at the backpack_id field. This means that the results are going to be lumped together in that order. The second query starts by using the GROUP BY against the backpack_id field right from the start.
To put this clause in simple terms, it lumps together same-value fields based on the order of the GROUP BY fields listed. So you see in the second Result Set, in the above image, that there are 2 rows with a backpack_id value of 10101 and so these 2 are lumped together. Since there are so few field-value pairs (values in the same field) that are equal, the GROUP BY clause does little more if other fields are selected first.
This clause is very important in programming, reporting, analytics, and just for aesthetic appeal since it makes like-like values rest next to one-another. The next thing you need to do is to sort them out. So if you had a Table (remember, this is where all the data rests, like a spreadsheet with rows and columns) called CartoonNetworkGroupies and you wanted to separate them by Gender (assuming these 5 fields in the Table: first_name, last_name, cartoon_name, character_gender, cartoon_mood) you could use the following query to do so:
SELECT first_name, last_name, cartoon_name, character_gender, character_mood
FROM CartoonNetworkGroupies
GROUP BY character_gender, first_name, last_name, cartoon_name, character_mood
... or if you wanted to lump them together by the cartoon that they're in:
SELECT first_name, last_name, cartoon_name, character_gender, character_mood
FROM CartoonNetworkGroupies
GROUP BY cartoon_name, first_name, last_name, character_gender, character_mood
...and if you wanted to lump them into groups contained by gender then by cartoon:
SELECT first_name, last_name, cartoon_name, character_gender, character_mood
FROM CartoonNetworkGroupies
GROUP BY character_gender, cartoon_name, first_name, last_name, character_mood
Note: In SQL Server you must use the entire list that you have in the SELECT clause in the GROUP BY clause, otherwise it throws errors... this does not apply to aggregates (SUM, COUNT, MAX, MIN, etc. which we will go over later in the series).
ORDER in the Court!
Now that you have all those characters lumped into groups, you might as well sort them out so you know which is most important to your needs. This is one of the clauses that will help in readability of your results more than anything else (my personal opinion). The ORDER BY clause allows you to identify which field to sort by, either using ASC for Ascending or DESC for Descending.
In the above image, you can see the 2 queries from above as well as a 3rd one that was added. This 3rd query adds on an ORDER BY clause that says this:
ORDER BY color desc
side-note: the desc should have been uppercase as a best-practice, but even I don't remember to do so all the time
The above basically tells us "Ok, now that we have those results, lets sort them in reverse order from biggest to smallest." In this case, since the field uses aphabetic characters, it returns values from Z-A... this is why the first color field value in the 3rd Result Set above shows "white".
You can leave off the ASC or DESC modifier without consequence. If you do leave it off, it will assume and Ascending sort order. This can, of course, get tricky if you need to group and sort by multiple columns in strange ways, so watch how your lists read.
As a last note, you will notice that I didn't include the full list of fields in the ORDER BY clause... you don't need to.
So there you have it, a solid start to sorting things out in SQL. With this knowledge, you should now be able to make some pretty decent queries and practice up on how things show up and return. Going forward I plan on introducing the use of _DISTINCT, UNION, ISNULL(), TOP, LIKE, BETWEEN', aggregate functions, wildcards, and aliases.
Please feel free to return to prior Posts in this series (listed below) to refresh yourself on the fundamentals:
SQL Beginner's Tutorial: Relational Databases & SQL
SQL Beginner's Tutorial: Writing your First Queries
SQL Beginner's Tutorial: SELECT Data to Use in the Game
SQL Beginner's Tutorial: Manipulate Data for Use in the Game
SQL Beginner's Tutorial: JOIN Me and Together We Will Rule the Universe
SQL Beginner's Tutorial: What if the World IS NOT NULL? and IN the Galaxy?
