sql inquiry

[maytel]

i have 2 tables A and B is it possible in sql to get from B the last occurence (based on date) for each record in A? ex A cust1 cust2 B cust1 01012006 10.00 cust1 01022006 20.00 cust2 01022006 30.00 cust2 01032006 40.00 output cust1 01022006 20.00 cust2 01032006 40.00 tnx in advance maytel

[Mahbub8957]

Please check is it work or not: Select custID, Max(date field) from B group by custID Where custID in (Select Distinct custID from A) Mahbub8957

[ver082874]

i forgot one detail let me rephrase is it possible in sql to get data from B (based on date SPECIFIED by the user) for each record in A? date specified by user 01012006 A cust1 cust2 B cust1 01/01/2006 10.00 cust1 01/02/2006 20.00 cust2 12/31/2005 30.00 cust2 01/03/2006 40.00 output cust1 01/01/2006 10.00 cust2 12/31/2005 30.00

[Matthew Hazlett]

Your question is not very clear, but I think you want somthing like this: select b.* from b, a where a.user = b.user and b.date = '01/01/05'; Matthew Hazlett

[Guffa]

Join in the B table and filter out the dates after the specified date. Group by the fields in the A table and use max() to get the latest date from the B table. --- b { font-weight: normal; }