Queries: Getting a related field from a GroupBy (total) query

In a total query, we can easily get "one" field, the one on which the aggregation occurs, but how can we get the other fields of the "same" record.

With the three fields: BookID, DateOut, BorrowerID

for each BookID, I can easily get the lastest time it has been taken out, but how can I get "by who"? If you think that GroupBy on BookID, Max on DateOut and Last on BorrowerID is the solution, you may get surprises... it is not: Max on DateOut can be for a different record than Last on BorrowerID, a little bit like Min on BorrowerID would be. Also note that Last mean the last record seen by the engine, for that given group, and this is highly dependant of the strategy of resolution used by the engine. It is better to see Last as "an undefined representation of this group".

Back to our initial question, how can we get the "latest" BorrowerID for each book? How do we get the number of pages of the lastest book publish by each author, who is the supplier giving the best price for each item, etc.?

There are many solutions for that. Maybe the easiest one is to use cascading queries.

1. Cascading Queries
   1. Define a new query.
   2. Bring the table.
   3. Push the summation button on the toolbar to make it a total query.
   4. Bring BookID in the grid, keep GroupBy.
   5. Bring DateOut, change its GroupBy to Max.
   6. Save the query, Q1.
   7. Define a new query, bring the table and bring Q1. Keep it a simple select query (not a total query).
   8. Join, if not already done, the two fields BookID.
   9. Join the DateOut field with Q1.MaxOfDateOut.
   10. Bring BookID and BorroweID in the grid.

   That's the solution.

   Someone may prefer a single query. In this case, you can use a sub-select query.

2. Sub-Select Query

   1. SELECT Q.BookID, Q.BorrowerID FROM TableName As Q

   Up to now, it is ok. We just use an alias, Q, for the table name. Now, in additon, we need a WHERE clause: WHERE DateOut = the latest dateOut for this bookID, so, let's write it in SQL:

   2. WHERE DateOut = (SELECT Max(T.DateOut)
                      FROM TableName As T
                      WHERE T.BookID = Q.BookID)

   You see that we simply bring another copy of TableName, alias it T, note how we specify "for THIS bookID", and since the quantity getting out of the (SELECT ) is a scalar (Max of something), not a full recordset, DateOut = (SELECT ... ) is the same as DateOut = scalar, which is well defined (if it was not, we would have to use = ANY (SELECT ...), or = ALL(SELECT ... ) or something similar.

   So, the query is just:

   3.     SELECT Q.BookID, Q.BorrowerID
          FROM TableName As Q
          WHERE DateOut = (SELECT Max(T.DateOut)
                      FROM TableName As T
                      WHERE T.BookID = Q.BookID)

   Well, if you don't like sub-select query, we can still use a single total query, but it is a little bit more complex than the original one.

3. With a single Total query

   1. SELECT Q.BookID, First(Q.BorrowerID)
          FROM TableName As Q INNER JOIN TableName As T
                          ON Q.BookID=T.BookID
          GROUP BY Q.BookID, Q.DateOut
          HAVING Q.DateOut = Max(T.DateOut)

   Here again, note that we use two alias of the same table, and if Q.DateOut=Max(T.DateOut) is self documented (we have to use the HAVING clause, not a WHERE clause, since Max is involved), note how the inner join express the notion "the max occurs only for 'this' book". Finally, since we are using a total query, Q.Borrower has to be "aggreated" somehow (we can use Min, Max, First, Last... they produce the same result, since they operate on a single record, anyhow).

   With Jet 4.0, we can use what appears an easier formulation.

4. Sub-select in the FROM clause

   While solution 3 presents a Total query embedding an inner join, we can, in Jet 4.0, reverse the roles.

   1. SELECT Q.BookID, Q.BorrowerID
      FROM TableName As Q INNER JOIN
              (SELECT BookID, Max(DateOut) As S
               FROM TableName
               GROUP BY BookID)  As T
              ON Q.BookId=T.BookId AND Q.DateOut = T.S
      

   The idea is to make an inner join with a table, aliased as T, that will supply the latest date for each book. In fact, it is just the same as solution 1, but with a single query.