Picking latest 'duplicate' entry and linking to second table

[steveclements]

I think I have a straight forward requirement here but cannot seem to work out the logic.

I have 2 tables with the following columns

SERVERLICENSE
licenseid,licensekey,createddate,status (active or dormant)

TOOLICENSE
toollicenseid,licenseid,toolname,licensekey,create ddate

The tables are linked via the licenseid. Typical data is:

SERVERLICENSE
1,1235-2563,10/01/11,dormant
2,5685-5365,05/01/12,active

TOOLLICENSE
1001,1,smtp,adfg-fgbh,10/01/11
1002,1,odbc,erth-bgfh,10/01/11
1003,1,html,rfgt-dsww,10/01/11
1004,2,word,edfg-tghj,05/01/12
1005,2,smtp,wwsk-plon,05/01/12

From this data you can see that some of the tools are linked to Serverlincense 1, others are against serverlicense 2, and an updated toollicense has been created against serverlicense 2.

I want to update the toollicense table (or create a new one) that combines the distinct tools and latest updated tools and links them to the latest server license. This means I will end up with the folloing TOOLLICNSE table

TOOLLICENSE (NEW)
1002,2,odbc,erth-bgfh,10/01/11
1003,2,html,rfgt-dsww,10/01/11
1004,2,word,edfg-tghj,05/01/12
1005,2,smtp,wwsk-plon,05/01/12

SO the odbc & html tools have bbeen updated to link to serverlicense 2, and the earlier smtp tool has not been imported into the new table.

Hope this makes sense...

Thanks

Steve

[Rabbit]

I would take the max tool id by each tool name and use an inner join to filter down to the most recent records. Then, for the license id, you just need to select the max license id.

[steveclements]

Thanks for the reply Rabbit, I'll take you suggestions and try them out