CODEWITHSUNDEEP
sql
J. Simpy
J. Simpy

Reputation: 11

How to display customers with latest transaction date

I need help in my project for generating a report. It's simply displaying a list of customers with their latest date of transaction. I used left join for tables CUSTOMER and TRANSACTIONS.

FROM CUSTOMER CUST
LEFT JOINT TRANSACTIONS TXN
ON TNX.CUST_ID = CUST.ID

Of course it will display multiple records of customers based on how many transactions a customer have. How can I get the latest transaction per customer only? Thanks in advance!

Upvotes: 1

Views: 5851

Answers (5)

Wintergreen
Wintergreen

Reputation: 234

SELECT *
  FROM (
      SELECT customer, transactiondate
       FROM abc
      ORDER BY transactiondate)
 result
 GROUP BY customer,transactiondate

Upvotes: 0

Darko
Darko

Reputation: 11

From the SQL you have posted it would seem that the Transactions table already contains the Customer ID and the transactions. I'm guessing the reason you want to join these tables is to reference a Customer by name rather than ID. In which case your SQL needs to look something like this:

SELECT DISTINCT
CUST.NAME, --Your field names may differ
TXN.TRANSACTION_DATE --Your field names may differ

FROM 
CUSTOMER CUST
LEFT JOIN TRANSACTIONS TXN
ON TNX.CUST_ID = CUST.ID

ORDER BY
TXN.TRANSACTION_DATE DESC

As JohnHC has stated, it would be more helpful if you provided more details on the data in the tables.

Upvotes: 1

G.Arima
G.Arima

Reputation: 1171

Try this after you join the data. abc data is the final data where there are multiple transactions for each customer. 

select distinct customer,min(transaction) as b from abc
group by customer;

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

Use max

select Cust.SomeColumn, 
       max(txn.SomeDateColumn) as MaxDate
FROM CUSTOMER CUST
LEFT JOINT TRANSACTIONS TXN
ON TNX.CUST_ID = CUST.ID
GROUP BY Cust.SomeColumn -- include every column that isn't being aggregated

Upvotes: 1

Hannes
Hannes

Reputation: 482

You probably can use something like this:

SELECT MAX(TNX.Date)
FROM CUSTOMER CUST
LEFT JOINT TRANSACTIONS TXN
ON TNX.CUST_ID = CUST.ID
GROUP BY CUST_ID

Upvotes: 1

Related Questions