Complicated join

I'm trying to write a view in SQL2000 (it's my client's environment, and they're not ready to upgrade).

I have a table called "Customers" and another called "CreditCards." One customer may have many credit cards.

The "CreditCards" table has a varchar field "ExpDate" and the values in this column are all four digits, mmyy.

I am trying to figure out how to write a view that returns one line per customer, showing the credit card details with the latest expiration date.

For example, Customer John Doe has two cards. One expires 0313 and the other expires 0514. I want my view to show one line for John Doe, with the details for the 0514 card.

Can anyone think of a good way to do this?

Thanks!

A good place to start would be to flip the year and month around and take the max expiration date before joining to the Customers table. Once you have the expiration date formatted as yymm instead of mmyy, then a max() will return the proper record.

Great idea, Joe, thanks!