SQL question

[sarta]

Say I have a customers table and a history table

CUSTOMERS TABLE
customer_id  name
1            Bob
2            Ted

HISTORY TABLE
history_id  customer_id  status  date
1           1            2       3/1/08
2           1            3       3/4/08
3           1            4       3/2/08
4           2            2       3/1/08

I only want to see the latest status for each customer, but this query pulls up EVERY status for each customer:

SELECT customers.name, history.status
FROM customers, history
WHERE customers.customer_id = history.customer_id

What is a query that will pull up each customer name, showing the SINGLE latest (most recent) history record for each customer? (Interested in ANSI solution as well as ORACLE 9i)

 

[PHV]

SELECT C.name, H.status
FROM customers C
INNER JOIN history H ON C.customer_id = H.customer_id
INNER JOIN (SELECT customer_id, MAX(date) LastDate FROM history GROUP BY customer_id
) L ON H.customer_id = L.customer_id AND H.date = L.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sarta]

Great, thanks!