I need to join two tables to find the first instance in the second table, however as I am using Teradata I can't use temporary tables, table variables or recursion.
I have two tables - let's call them Customers and Purchases
01 Andy Anderson
02 Bob Bobman
03 Chris Cross
04 David Daverton
productid customerid datepurchased
02 01 01/01/2001
02 04 02/02/2002
01 01 05/05/2005
07 04 07/07/2007
06 03 04/04/2004
I need to left join customers to purchases to bring back details of their first purchase - i.e.
customerid name product_first_purchased date_of_first_purchase
01 Andy Anderson 02 01/01/2001
02 Bob Bobman NULL NULL
03 Chris Cross 03 04/04/2004
04 David Daverton 02 02/02/2002
As I said above I can't use any form of temporary table or table variable - it needs to be done in one breath.