How do I use "LAST" in query?

htmlasp

Registered User.
Local time
Today, 06:31
Joined
Oct 9, 2013
Messages
10
I have a table with 4 fields

PaymentID(autonumber PK) / PaymentPayer / PaymentDate / PaymentAmount

I want to do a LAST of query on PaymentDate to find the last payment date for each customer

When I set LAST under totals in the query on the Pay mentDate field it returns every row as if LAST is not even set

If I have John Smith paid on 4/2/14 and 4/15/14 I only want to show the 4/15 payment for John Smith.
 
Another strategy is to sort by PaymentDate DESC, and then select only the first record--which will be the last. . .
Code:
SELECT TOP 1 * FROM YourTable ORDER BY PaymentDate DESC
 
Hi Mark,

Thanks for your reply. I am trying to get the last payment date for each customer. If I use the method you describe I'm only getting 1 record...the last payment date of any customer.

If I have 3,000 customers and 25,000 payments I want 3,000 records returned...the last payment date for each of those 3,000 customers.

thanks again!
 
Instead of Last function, use the Max function, (on the date field).
 
This requirement almost always requires two queries or the use of subquery
Query one is a simple aggregate or totals query to to get the max date per customer ie max on date and Groupby on customer, this query is joined back to the table on both fields to enable you to get the rest of the data in a second query.

Last is a strange function that has little use in a relational database as it is based on record position, not what you think it infers.

Brian
 
Try this:

Code:
SELECT PaymentId, PaymentPayer, PaymentDate, PaymentAmount
FROM YourTable AS t
WHERE PaymentDate =
  (SELECT MAX(PaymentDate)
    FROM YourTable
    WHERE PaymentPayer = t.PaymentPayer);

Just to underline what Brian said: FIRST and LAST are practically useless. Typically when you see FIRST or LAST used at all it means the person writing the query either didn't understand what these functions do or didn't even understand SQL. Avoid the FIRST and LAST functions.
 
This is the query Brian described:
Code:
SELECT PaymentId, T.PaymentPayer, PaymentDate, PaymentAmount
FROM YourTable AS T
INNER JOIN
     (SELECT MAX(PaymentDate) AS MPayDate, PaymentPayer
      FROM YourTable
      GROUP BY PaymentPayer
      ) AS P
ON T.PaymentPayer = P.PaymentPayer)
AND P.MPayDate = T.PaymentDate;

It would have a problem if more than one payment was taken on the same day by the same payer.
 
Or a correlated subquery:

Code:
SELECT PaymentPayer,
   (SELECT TOP 1 PaymentID 
    FROM YourTable 
    WHERE PaymentPayer = T.PaymentPayer 
    ORDER BY PaymentDate DESC
    ) AS LastPymentID
FROM YourTable AS T
 
It would have a problem if more than one payment was taken on the same day by the same payer.

Doesn't he have a problem anyway if there is more than one payment per customer on a given day unless there is a time in the date field?

Brian
 

Users who are viewing this thread

Back
Top Bottom