If you have not read part 1, below is the direct link so you can have a read at what QUERY is, and how it can help your case, whether it be building Glide apps or make your normal day work easier.
6/Dynamically sort your data using ORDER BY
An ORDER BY claused can be used to dynamically sort your data after being queried, and you can change the column that is used to sort with just a letter.
By default, the ORDER BY sorts ascendingly, and it can grab the data type in the column automatically. Below is an example where I sorted the data on the left by column B, ascendingly, by alphabetical order.
And here’s how it goes when I change the sorting column to C.
So how do you sort descendingly? The answer is you add a DESC at the end of the ORDER BY (and for ascending it’s ASC).
7/Top 5, Top 10, Bottom 5, Bottom 10? We can do it all with LIMIT
This would be useful in many cases where you want to highlight the top performing employee in your company, the ones who earn most, the worst performing SKUs in your marketplace, etc.
We still using ORDER BY here, just add a LIMIT clause at the end to make the “Top”/“Bottom” thing work.
For example, here are the people in the top 5 for highest salary in my example.
And here are the top 2 only for Amazon.
And the bottom 7 overally.
8/GROUP BY - side by side with aggregated functions
In the 1st part, I have written about the aggregated function, and to make it work in a pivot style, we need the GROUP BY clause.
The rule of thumb here is: “every column in the SELECT clause (i.e. before the GROUP BY) must either be aggregated (e.g. counted, min, max) or appear after the GROUP BY clause” - as Ben Collins wrote.
I have extended my example to include the year of birth and month of birth for examples of this part.
Let’s say I want to know the average salary by year of birth, sorted ascendingly, the formula would be.
Or if we want to see the max salary by company, sorted descendingly, here’s how it goes.
To wrap this up, I show a table counting the number of people that was born in each month, by company.
9/Resources to learn further
There are a lot more of things to learn, that you can read via this article - about data manipulation inside QUERY.
Also, these videos from Ben Collins might help.
Hope the 2 tutorials about QUERY would help you a lot in your specific use cases!
