Urgent ! Question on using First function

[Ben]

Hi,

I tried to use first function in my query to select on the last order of a
customer but the result return with the first orderdate record.
What I want is the last Order date records. How do I sort the orderdate by
desc by using first function ?
My qeury look like this :

Select First(CustomerCode), First(OrderDate), Sum(Qty)
From CustOrder
Group By CustomerCode.

Thanks in advance for any kind reply.

Ben

 

[John Vinson]

Hi,

I tried to use first function in my query to select on the last order of a
customer but the result return with the first orderdate record.
What I want is the last Order date records. How do I sort the orderdate by
desc by using first function ?
My qeury look like this :

Select First(CustomerCode), First(OrderDate), Sum(Qty)
From CustOrder
Group By CustomerCode.

The First() function WILL NOT WORK for this purpose. It returns the
first record *in disk storage order* - an order which you cannot
control. It's pretty nearly useless unless you simply want an
arbitrary record.

In order to retrieve the most recent order date, use the Max()
function instead:

SELECT CustomerCode, Max([OrderDate]) As MostRecentOrder, Sum([qty])
AS TotalQty
FROM CustOrder
Group By CustomerCode;

Note that since you're grouping by CustomerCode there is no need to
use any aggregate function for that field.

 

[elsa]

When you work with dates, they are the same as numbers.
Rather use MAX to het the most recent date and MIN to get
the first date.

Hope this helps