How to get reliable results from first() and last()?

Question

EDIT: See edit below for explanation of why min() and max() are NOT adequate.

=========================

The MS documentation on the functions first() and last() says “Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary.”

Obviously, that makes these functions pretty useless for their intended purpose unless the query includes an ORDER BY. But including that in the query is not a straightforward thing to do because these are "aggregate" functions, so a query that SELECTs on them cannot ORDER BY any other field that is not also submitted to an aggregate function.

I have found that a query based on a single table generally returns results in the order of that table’s primary key. But apparently, that cannot be relied on to always be true and may fail under certain circumstances. There's an excellent discussion of this issue in an article, DFirst/DLast and the Myth of the Sorted Result Set.

That article offers two solutions to this problem:

Option one; you first use the DMin/DMax-Function to retrieve the value from the “sortable” column ... and use this as an additional criterion to your query to retrieve the target record.

Second option; you first create a query just containing the primary key and the max value of the sortable column (e.g. CustomerId and maximum of order date). Then you create a second query on the orders and join the first query in there on these two fields. The results will be all column from the orders table but only for the most recent order of each customer.

Those instructions are pretty complicated, so I'd need to see an example of them implemented in code in order to trust myself to use them myself.

This issue has got to be very common because a lot of businesses need to know the first or last order by a customer that meets some condition. But when I Google "Access query first last "order by"", there are several results that explain the problem, including on StackOverflow, but none that lay out a solution with sample SQL code.

What is the right way to do this, including sample code of doing it?

=========================

Edit:

Many sources online, as well as the comment below by Gustav and the proposed answer by Albert D. Kallal, say you can just use min() and max() instead of first() and last(). Obviously, that's okay if what you want is the value of a field in the record in which that field has the smallest or largest value. That's a trivial problem. What I'm talking about is how to get the value of a field in the record in which some other field has the smallest or largest value.

For example, in the answer by Albert D. Kallal, he wants the first and last tour for each customer, so he can just use min() and max() on the dates of the tours. But what if I want to know the location of the first tour for each customer? Obviously, I can't use min(location). If first() would work in a sensible way and if table [Tours] has the primary key [Date], I should be able to use something like:

(SELECT first(location) from [Tours] where [Customer] = ID_Customer)

I am using code like that and it usually gives me the right answer, but not always. So that is what I need to fix. I understand that I may need to use min() instead of first(). But how do I use min() for this since, as I said, I obviously can't just use min(location)?

Answer 1

Never really grasped what first() and last() does in Access.

As you note, rather common to want say last invoice or whatever.

So, say we have a table of Tours. I want the first tour date, and the last tour date.

Well, this query works:

 SELECT MAX(FromDate) as LastTourDate, min(FromDate) as FirstTourDate
 FROM tblTours
 WHERE FromDate is not null

When I run above, I get this:

So, that gets you the min, and max - and gets you this in one query.

No real need for a order by.

However, often there are more then one table involved.

So, I might in place of JUST the first and last tour date?

I probably want a list of customers, and their first tour they took, and say their last tour. But, then again, that's a different question.

But, you again can order your main table ANY way you want, and still pluck out (pull the min and max).

So, you can do it this way:

Say, tblMain client (people - customers whatever). Say, tblMyTours - a list of tours they took (child table).

So, the query can look like this:

SELECT tblMainClient.FirstName, tblMainClient.LastName,
(SELECT Min(FromDate) FROM tblMyTours 
 WHERE tblMyTours.main_id = tblMainClient.id) 
    AS FirstTourDate,
(SELECT MAX(FromDate) FROM tblMyTours 
   WHERE tblMyTours.main_id = tblMainClient.id) 
    AS LastTourDate
 FROM tblMainClient

so, the main query is still tblMainClient - I can order, filter, sort by any column in that main table, but we used two sub-query to get the first tour date and the last tour date. So, it will look say like this:

So, typical, we can use a sub-query, pull the max (or min) value, but restrict the sub query to the one row from our parent/main table.

edit: Get last reocrd, but SOME OTHER column

Ok, so say in our simple example, we want the last tour, but NOT the date, but say some other column - like say the last Tour name.

Ok, so we just modify the sub query to return ONLY the last reocrd, but a different column.

And since dates (say 2 invoices on the same day, or yearly tours might have the SAME name, then we need to ensure that ONLY one reocrd is returned. We do this by using top 1, but ALSO add a order by to be 100%, 200%, 300% sure that ONLY ONE top record is returned.

So, our query to get the last tour name, but based on say most recent tour date?

We can do this:

SELECT FirstName, LastName, 
 (SELECT TOP 1 TourName FROM tblMyTours 
   WHERE tblMyTours.main_id = tblMainClient.id
   ORDER BY tblMyTours.FromDate DESC, tblMyTours.ID DESC)
 AS LastTour   
 FROM tblMainClient

And that will give us the tour name, but the last one.

This:

So, you ceratinly not limited to using "max()" in that sub query.

However, what happens if we want the Tour Name, Hotel Name, and City of that tour?

In other words, it certainly reasonable that we may well want multiple columns.

There are more ways to do this then flavors of ice cream.

However, I like using the query builder for the first part.

What I do is use the standard query builder, do a join to the table and simple slect all the columns I need.

So, for above tblMainClient, and their tours from tblMyTours?

I build a join - use query builder like this:

So, note how I added the columns TourName, FromDate, HotelName and city from that child table (tblMyTours).

Now, of course the above will return 10 rows for anyone who gone on 10 trips.

So, what we do is add a WHERE clause to the child table, get the LAST pk "id" from tblMyTours, and restrict that child table to the ONE row.

So, the above query builder gives us this:

 SELECT tblMainClient.ID, tblMainClient.FirstName, tblMainClient.LastName,
 tblMyTours.TourName, tblMyTours.FromDate, tblMyTours.HotelName, tblMyTours.City
FROM tblMainClient 
INNER JOIN tblMyTours ON 
 tblMainClient.ID = tblMyTours.Main_id;

(but, I did not have to write above).

So, we add a where clause to that child table join - get the CHILD table "id" in place of TourName, or Tourdate).

So above becomes this:

SELECT tblMainClient.ID, tblMainClient.FirstName, tblMainClient.LastName,
 tblMyTours.TourName, tblMyTours.FromDate, tblMyTours.HotelName, 
 tblMyTours.City
 FROM tblMainClient 
INNER JOIN tblMyTours ON tblMainClient.ID = tblMyTours.Main_id

WHERE tblMyTours.ID = 
(SELECT TOP 1 ID FROM tblMyTours
 WHERE tblMyTours.Main_id = tblMainClient.id
 ORDER BY tblMyTours.FromDate DESC, tblMyTours.ID DESC)

Now, above is a bit advanced, but OFTEN we want SEVERAL columns. But, at least the first part of the query, the two tables, and the join was done using the query builder - I did not have to type that part in.

so, if you want JUST one column - differnt then the max() critera, then use top 1 with a order by. Do keep in mind that ONLY ONE RECORD can EVER be retunred by that query - if more then one reocrd is returned, the query enginer will fail and you get a message to this fact.

So, for a produce bought, invoice date? They could by the 1 product 2 times, or 2 invoices on the same day might occur. So, by introduction of the 2nd ORDER BY clause (by ID DESC), then that top 1 will ONLY ever return one row.

So, which of the above two?

Well, if just one column from the child table - easy. But, if you want multiple columns? Then you could probably write up a "messy" solution, but I perfect to just fire up query builder, join in the child table, click on the "several" child values I want. Get the query working - and hey, it all up to this point 100% GUI.

Then we toss in the EXTRA criteria to restrict that child table row to the ONE last row, be it simple last one based on ID DESC, or say TourDate, or whatever.

And now we get this: