First(Column) w/ Order By gives different result than Min(Column)

[Dor100]

Should this be happening? Using Acc XP; here's the scene:

-simple date field DateID, format Short Date, no nulls

-lowest date is 08/06/2004, next row 08/06/2004, then 08/09/2004, 08/12/2004

-highest date is 10/19/2004; about 200 rows of other October values separate it from 09/30/2004

-"select first(dateid) from Table order by dateid" should yield 08/06/2004, the same as "select min(dateid) from Table order by dateid" or just "select min(dateid) from Table"; instead, the first function with and also without the Order By gives 08/12/2004; the same thing in reverse is happening for Last vs. Max. Last both with and without Order By gives 09/30/2004.

Shouldn't first(column) with Order By and last(column) with Order by yield the same result as Min and Max in such a case?

 

[JonFer]

ORDER BY sorts the results of the query, not the incoming records, so don't think of that in conjunction with any of the aggregate functions. ORDER BY is the last step of the query processing.

First(FieldName) gives the value of FieldName on the first record in the group based on the order of the records in the table. Min(FieldName) gives the lowest value of FieldName in the group.

I avoid First() and Last() because the results are unpredictable and it goes against the general rule that record order should be unimportant in a relational database.


John

http://www.interautomateconsulting.com

 

[Dor100]

Thanks, John.

>>"I avoid First() and Last() because the results are unpredictable and it goes against the general rule that record order should be unimportant in a relational database."

Unpredictable - you can say that again. I think I'll avoid it as much as possible, too, since even in this case First should yield the same as Min (08/06/2004), and the 09/30/2004 seems quite off the map.

 

[JonFer]

A primary or unique index will be used to sort the table records upon opening. If you delete all indexes from the table and any saved Order By statement (look at Table Design properties) and then open the table, the records should display in the order they were added so the first record will probably have the 8/12 date.

John

http://www.interautomateconsulting.com