You need a GROUP BY which ensures only 1 record per ddate and an ORDER BY ddate. So something like:
select cust.icustiid, cust.cname, trx.ddate, trx.nAmount ; from cust JOIN trx ON whatever ; WHERE ddate < vp_date group by 1,3 ORDER BY ddate DESC kam wrote: > 2 tables, customer and transactions. > > select cust.icustiid, cust.cname, trx.ddate, trx.nAmount ; > from ; > cust, ; > trx ; > group by 1,2,3,4 > > I didn't test that but it is like what I am using. > > The problem is that most customers have many transactions. I only want 1 trx > - the latest transaction which is <= vp_date. > > so for example if Larry Smith has 4 trx: > 01/05/2007 > 02/10/2007 > 04/20/2007 > 10/15/2007 > > If vp_date is 05/01/2007 then I would only see the transaction dated > 04/20/2007. It seems like I should use MAX() and maybe TOP 1 > but I have tried every combination I can think of. > -- Cheers ============ Brian Abbott ============ _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
