Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

Query Pattern: Finding the maximum/minimum and getting the full row

Authored by Ben Clothier

The other day Juan needed my help with a query that required a special technique commonly known as the Strawberry query. He needed a way to match one group of records with the first occurrence of a item in another table. From that I decided to write this post to share the technique.

Access can work great in large companies, call us to discuss

There are times where we want to not just know that a maximum or minimum value in a table but also know which row contains that maximum value. This can be a challenge as SQL does not make it easy to express such a question, but strawberry query is a wonderful answer to that question. To help appreciate the technique, we’ll be using a running example. Suppose we have a company that collects and sells rare & highly prized collector items and we want to know who is the best performing salesperson. Here’s our Sales data for that hypotehical company.

 

 

Typically, When we want to know what was the largest sales amount in a given month, we can ask:

 

This query would give us something like this:

But let’s ask – which salesman had the biggest sales each month? This wouldn’t work:

The query is actually equivalent to “give me all sales staff’s largest sales per month”. That’s a different question from “who was the salesperson that made the biggest sales in this month?”.

Instead of getting only a row per month, we got a row for numbers of month times numbers of salesperson and thus 6 rows too many for this example. Fortunately, we can express this question using the ‘strawberry query.’

Strawberry Query to the rescue

A strawberry query has these essential components:

1) it is usually a self-join within one table
2) the join criteria has more than one column being compared
3) one of those join criteria is an inequality operator.
4) the outer table’s column must evaluate to NULL.

So, to get the SalesPerson, (Full Name field below), from tblSales where the sales amount was largest for the month, we can use this query:

The query will produce this result:

 

Because the query did not use any aggregate function neither did it have a GROUP BY clause, we could then join the SalesPerson table, include the full name for the display as well as the actual Sales Date from the Sales table which would have been invalid in an aggregate query.

How does the strawberry query work?

The expression “Month(s.SalesDate) = Month(c.SalesDate)” is functionally equivalent to doing a “GROUP BY Month(SalesDate)”, and that is how we’d define our “grouping” when we use a strawberry query. The second expression, “s.Amount < c.Amount” in conjunction with the “WHERE c.Amount IS NULL;” is equivalent to doing a “MAX(Amount)”. If you’re wondering how the “c.Amount IS NULL” expression could help us extract the minimum or maximum amount, it may help to look at the query if we removed the WHERE clause.

First, we’ll reduce the query to essential skeleton of the strawberry, removing the tblSalesPerson table and other columns then include the outer columns.

 

Here is the listing of the result query would return without the WHERE clause: (lower sales were omitted to reduce the number of rows shown)

A row from left table will be matched with any other rows in right table where the amount is greater. You can see how all sales that weren’t the largest sales get matches with each other’s sales that had a larger sales amount. The only row that can’t match any other sales is when it is the largest amount, and we would get a NULL from the outer sales table’s column. Hence the WHERE clause filters the set down to only those rows and we get our maximum row.

Closing Notes

Use SQL View for Strawberry Queries

Because the strawberry query uses an inequality in the join criteria, Access Query Designer cannot represent so if you attempt to view the query in design, you’ll get an error. For those who love QBE, you can add all the tables you need, drop in fields, then give the tables an alias so that it’s a simple editing when you switch to SQL and add that inequality criteria.

Optimization

In the example, I used “Month(SalesDate)” which is not sargable and therefore the query isn’t as optimized as it could be. A sargable criteria would be among the lines of “SalesDate BETWEEN #yyyy-mm-dd# AND #yyyy-mm-dd#” but that wasn’t appropriate in this situation because that’d have limited us to only a single month when we want to query for each month for all possible sales. If one wanted to optimize the performance further using “group by months”, it may be necessary to consider approaches such as creating denormalized column to store the month number & indexing upon it or break the SalesDate column into SalesYear, SalesMonth, and SalesDay column. If we’re using different backends such as SQL Server, we can then use additional features such as persisted computed column or similar tools.

Having index on the column where you apply the criteria in the strawberry query will ensure that the query runs fast, handy when you start to deal with larger datasets.

If there’s any other query patterns you have noticed or used, we’d love it if you can share with us.

Publicado en Access Help

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*

 

Quienes Somos

ExpertosMicrosoftAccess.com es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores