Select the most recent records

[trashed]

Hi all.
I am working on a small credit/debt management tool.
I have a table that contains the main record for the debt: id, amount, client info, interest, paid back, etc

Then I also have a "history" table where all operations on a debt are stored: when it was first entered, when something is changed and so forth.

Debt table is as follows:

debt_id  	int(6)
data_insertion  timestamp
debt_date 	date
amount	 	float(7,2)
paidback 	float(7,2)
customer_id 	varchar(6)
agent_id 	int(4)
PRIMARY_KEY(debt_id)

History:

operation_id  	int(6)
data 	timestamp
amount 	float(7,2)
customer_id 	varchar(6)
debt_id 	int(6)
optype tinyint(1)
PRIMARY_KEY(operation_id)

How can I select the date of the most recent record in "history" for each "debt_id"?
Thanks

[urtrivedi]

select a.debt_id ,a.data_insertion , a.debt_date,a.amount,
a.paidback,a.customer_id,a.agent_id,
c.operation_id,c.data,c.amount,c.customer_id,c.debt_id,c.optype
from debt a left outer join 
		(
			select debt_id,max(operation_id) as last_operation_id 
			from history group by debt_id
		) b on a.debt_id=c.dept_id
left outer join history c on b.last_operation_id=c.operation_id

[caseyfw]

The above code is wrong! There's a typo on line 8. It should be:

SELECT a.debt_id,
    a.data_insertion,
    a.debt_date,
    a.amount,
    a.paidback,
    a.customer_id,
    a.agent_id,
    c.operation_id,
    c.data,
    c.amount,
    c.customer_id,
    c.debt_id,
    c.optype
FROM debt a
LEFT OUTER JOIN (
    SELECT debt_id,
        max(operation_id) as last_operation_id 
    FROM history GROUP BY debt_id
) AS b ON a.debt_id = b.dept_id
LEFT OUTER JOIN history AS c  ON b.last_operation_id=c.operation_id

[Jayavardhan]

Mr.Caseyfw and urtrivedi, Where you specified that

b.last_operation_id

? You both are used

max(operation_id) as last_operation_id

. But Operation_id is refered with c. That is C.operation_id.
And, Caseyfw where you found the mistake in urtrivedi? His code was right. What was above 19 step? There is no dept_id in 'b' variable. Then how can you assign

AS b ON a.debt_id = b.dept_id

?. Urtrivedhi u forgot to add "ON" in the above steps somewhere. Otherwise, evertything fine.

[tesuji]

History:

operation_id  	int(6)
data 	timestamp
amount 	float(7,2)
customer_id 	varchar(6)
debt_id 	int(6)
optype tinyint(1)
PRIMARY_KEY(operation_id)

How can I select the date of the most recent record in "history" for each "debt_id"?
Thanks

Hi trashed

If you are only interested in getting data and for example debt_id of the most recent rows from your second table History, your can simply do this by way of correlated selects, as for example:

select a.data, a.debt_id  from History a
  where a.data  = (select max(b.data) from History b where b.debt_id = a.debt_id) order by a.data, a.debt_id;

(This only functions if debt_id is not a unique key of table History, what is obviously true because operation_id is the only unique key of this table by definition.)

-- tesu

[Jayavardhan]

The above query was also fine. He directly get the date of most recent records.