INTRODUCTION
GOOGLE SHEETS is a free SPREADSHEET APPLICATION for personal use and it is getting a good attention these days.
There are many useful functions which are found in almost all the spreadsheet apps like Google Sheets or Microsoft Excel or Open Calc and so on but every application has a certain special portion which is unique from the others.
One of such features in GOOGLE SHEETS is the use of the QUERY FUNCTION which enables us to use the SQL SERVER type database queries on our data.
This QUERY LANGUAGE IS KNOWN AS THE GOOGLE VISUALIZATION API QUERY LANGUAGE and is almost similar to the SQL query language.
There are many standard clauses which are required to be understood so that we can make use of this powerful function easily.
In this article , we are going to focus on the GOOGLE SHEETS QUERY LANGUAGE clause ORDER BY and learn its use.
PURPOSE OF GOOGLE QUERY LANGUAGE CLAUSE- ORDER BY
Every clause in Google Sheets query language or Google query language or Google visualization api query language has specific task to do.
One such requirement is to sort the data as per our requirement when we sought the data from the data using GOOGLE QUERIES.
This particular situation is handled by the ORDER BY clause very easily.
THE ORDER BY QUERY SIMPLY SORTS THE RESULT AS PER THE PRIORITY SET BY THE QUERY FROM SMALLER VALUE TO THE LARGER VALUE OR ALPHABETICAL.
For example,
If we have two columns A and B where A contains the numerals and B contains the text.
If we use the query ORDER BY B,A , it’ll sort the data of the column B first i.e. Alphabetical and after that the data of the Column A will be sorted as per their values in the ascending order.
We’ll try to understand the usage with the help of a few examples.
EXAMPLES:
EXAMPLE DATA:
Let us take a random data and try out our newly learned clause – ORDER BY.
EXAMPLE 1 : DISPLAY THE TABLE WITH ONLY EMPLOYEE , ATTENDANCE AND RATING WITH THE RESULT SORTED BY ATTENDANCE
SOLUTION:
Let us try to extract the data and sort it as per the requirement.
FOLLOW THE STEPS TO CREATE A TABLE WITH ONLY EMPLOYEE, ATTENDANCE AND RATING, SORTED BY ATTENDANCE
- Select the cell where you want to get the result by double clicking it.
- Enter the function as =QUERY(B5:F16,”SELECT B,C,E ORDER BY E”,-1).
QUERY EXPLANATION: The QUERY used is =QUERY(B5:F16,"SELECT B,C,E ORDER BY E",-1). SELECT will make the chosen columns to appear in the result. We chose two columns. B, C AND E) , it means B,C and E columns will appear in the result table. We use the phrase ORDER BY E which will order the complete data on the ascending order of E.
- Press Enter.
- The result will appear as shown in the picture below.
We can see that with the help of a single statement , we could get a complete table with the desired sorting order.
The worst performing employee is at the top whereas the best performing employee is at the bottom.
Extending the same query, let us try to get the NUMBER OF STUDENTS WHO OPTED THE RESPECTIVE SUBJECT.
EXAMPLE 2: CREATE THE TABLE WITH THE FOLLOWING SORTING. RESIDENTIAL CODE > ATTENDANCE > RATING > SALARY [ MULTI LEVEL SORT USING QUERY ]
The ORDER BY clause is not that difficult to understand. Simply put the column names in the series and the sorting will take place.
Let us take another simple example to tell them how to sort at different levels which we also know as MULTILEVEL SORTING.
FOLLOW THE STEPS TO SORT THE DATA PER THE DESIRED SORTING:
- Select the cell where you want the result.
- Enter the formula as
=QUERY(B6:D18,”SELECT C,AVG(D),COUNT(C) GROUP BY C”,-1)
- Press ENTER.
- The result will appear as shown in the picture below.
THE RESULT IS A COMPLETE TABLE WITH THE SORTING AS WE REQUIRED.
The ORDER BY clause or phrase is very easy to use. You can use it with any column by simply putting the order.
USING ORDER BY WITH OTHER CLAUSES
The order by clause , not only takes the column number but also the Functions like MAX(A) or AVG(c) etc. if we are using any other query too.
GROUP BY AND ORDER BY
Let us find out the Average attendance sorted by Attendance and grouped by the Residential code.
Follow the standard steps and enter the query as
=QUERY(B5:F16,”select F,avg(C) group by F order by avg(C)”,-1)
The query can be understood as The first argument is the data. The query means to select or show F and AVG(c) , group it on the basis of F which means one group will have one value and order it on the basis of Average of the attendance.
- As we press ENTER, the result will appear as shown in the picture below.
So, this was a small demonstration of using the Group By and Order by together.
In this article we learnt about the ORDER BY QUERY of GOOGLE SHEETS QUERY LANGUAGE, its purpose, usage and examples.
