How to use SQL Max in Google Sheets to find latest date
By
SpreadCheaters
Let’s consider the following dataset which contains the details of the sales orders and we wish to find the 5 latest orders placed by our sales reps in any region. We are going to do this by using SELECT with an aggregate function MAX inside the QUERY function.
Google Sheets is an online alternative to Microsoft Excel which provides almost all functionality as compared to Excel. In some areas Google Sheets has an advantage over Excel and one of those areas is the ability to write SQL-like Queries directly in Google Sheets. To write SQL-like queries in Google Sheets we need to use the QUERY function and then we can use different combinations of queries and clauses which will be dependent upon our requirement. In this tutorial we’ll learn how to use the MAX function to find the latest date from a dataset.
Step 1 – Create a Named Range
– As a first step create a named range. This will help us in referring to the data easily and in a clean manner.
– So select all data. Click on the Data tab and then click on Named ranges.
– This will open up a sidebar menu on the right side of the sheet. Write the name of the named range as Sales_Data. Be careful while choosing a name for the named range because it doesn’t allow spaces in the names.
Step 2 – Create the Query with SELECT, MAX and Group By
– Now comes the real part, we’ll now create the query to get the latest 5 orders data from our actual dataset.
– For this purpose we’ll use the following Query to select the Region, Rep, Item, Units and Total columns, then find the latest of the Order Date column by using MAX aggregate, grouped by Region, Rep, Item, Units and Total columns.
=QUERY(Sales_Data,”select max(A), B, C, D, E, G group by B, C, D, E, G limit 5″,1)
This above query resulted in the details of the latest 5 orders from our sales data and we can easily see that all last 5 orders were placed by the Central Region sales reps. It means that using these query functions can give us more insight to our data which can be very helpful in taking future decisions.
