Back from the vacation, I find I can't remember something. My problem is, for example, I have 2 tables. Table 1 has 3 columns: id, saledate and amount. Table 2 has just 2 columns: id and amount. Now I want to select the latest 'amount' for each 'id' from table 1 and insert into empty table 2. Is that possible to do it in one insert statement? What's the best way to do it anyways?

Thanks a lot!

In your table1, is id the primary key?

No, id and saledate are primary key. Any thoughts? Thanks anyways.

In the Table1, do you have a primary key from one colomn, i.e., TableID?

insert into table2
select id, max(amount)
from table1 t1
where saledate = (select max(saledate) from table1 where id = t1.id)
group by id;

The "max(amount)" and "group by id" return the maximum amount, should multiple amounts exist for the latest salesdate for a given id.

Yes, it works! Thanks a lot!