Urgent ! Question on using First function

  • Thread starter Thread starter Ben
  • Start date Start date
B

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
 
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.
Click to expand...

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.
 
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
 
Back
Top