First, Last, Min & Max Functions

[aziz rasul]

How are the First and Last functions different to the Min and Max functions.

If I have a table with values placed in a new table: -

3
5
1
2
4
6

the Min and Max functions produce 1 and 6 respectively. However the First and Last functions return values based on the order that the entries were made. The First function will return 3 and the Last function will return 6.

If I now place an index on the above field in ascending order so that I get:-

1
2
3
4
5
6

the First and Last function still return the same values as above, hence it seems to remember the order in which the entries were made AND IGNORES the actual values themselves and the order that they are in.

Hence my question really is what is the purpose of the First and Last functions? How and where is it used? The online help does say that you can obtain ambigious results but doesn't really explain why this should be. Any clarification would help.

 

[FoFa]

The order the data was stored is not changed because of the index. The order the data is stored is not always in the order it was entered. First/Last has more functionality in recordset processing and subselects where you pretty much have know row values you are trying to grab.

 

[aziz rasul]

Thanks for that.

 

[Pat Hartman]

Recordsets are unordered sets. Putting an index on a table does not immediately affect how data is stored and it does not affect how data is retrieved by a query. It does affect the order of rows when you open a table in datasheet view because the view is based on a query created behind the scenes by Access and if there is an index, Access includes an Order By clause to return the data in the order of the primary index. When a db is compacted, Access reorders all table rows into primary key sequence (if there is a primary key otherwise it just recovers space left by deleted records). The effect of this is similar to that of a clustered index for those of you who use products like Oracle and DB2.

Even after the db has been compacted, the order of rows returned by a query that does not contain an order by clause is as they say "indeterminate". This means that you cannot predict it. However, for small tables, the order will rarely vary so most people think there is something sacrosanct about row order.

The bottom line is - if order is important to you, you MUST use a query with an order by clause.

First() and Last() refer to physical order in a recordset. If the recordset is ordered by an order by clause, subsequent executions of the functions will return the same rows provided the recordsets contain the same records.

Min() and Max() refer to magnatitude and are most likely what most people want when they say first and last. You don't want the First() date, you want the minimum date.

An example that should make the difference clear is a recordset ordered by CompanyID. If you ask for the First() order date, you'll get the date from the record with the lowest company. whereas if you ask for the Min() order date, you'll get the date of the earliest order in the recordset. However, if you order the recordset by order date, both First() and Min() will return the same results.