How to pick the largest ID#?

[coco]

I'm working on a project. There is a question that I couldn't figure out how to write the query: What is the largest ID# for each Division?

For example: There are four divisions: MR, WHS, SS, LS. Each division has several ID# followed by a three-digits number in the ID# field. Like:

ID#
MR301
MR301
MR402
MR501
WHS401
WHS402
LS302
LS303
LS305

The output I need to get is:
MR501
WHS402
SS305

How to write this query? Your help will be really appreciated.

coco

 

[Jon K]

SELECT Left([TableName].[ID],Len([TableName].[ID])-3) & Max(Right([TableName].[ID],3)) AS ID
FROM [TableName]
GROUP BY Left([TableName].[ID],Len([TableName].[ID])-3);

.

 

[coco]

Thanks Jon. It works. Really appreciate.

coco