I am currently working on a Crystal 8.5 report that was working fine, until the client decided to make a change.

The scenario is simple. Pull the customer names and sales order numbers for any sales order entered within a date range. There are other fields involved but they are all in the one table.

Complication: I have no control over the database. The database is updated from a program that will take a sales order and if it is adjusted, it will duplicate the records, incrementing the ID number and marking the old one as cleared (without deleting it) and leave the new one visible in the system. The sales order number will be the same for both records. Oh, and if you fill a sales order and turn it completely into an invoice, it will also be marked as cleared.

So I am trying to find an SQL statement that will pull sales orders within a date range but if there are two records for the same sales order number for the same customer, I only want the one with the largest ID number.
Code:
Data
ID     SO#     Client
10     555      ABC Company
11     231      EFG Company
12     555      ABC Company
13     560      ABC Company
14     998      XYZ Company
15     222      DEF Company
So I want ultimately the following data only, sorted by customer and SO#. Note ID 10 was removed because it was edited, which created ID 12. So 12 is the latest version I want.

Code:
Data
ID     SO#     Client
12     555      ABC Company
13     560      ABC Company
15     222      DEF Company
11     231      EFG Company
14     998      XYZ Company
I know in Crystal I can just pull them all, group on SO#, sort descending and if the ID changes, conditionally suppress the printing. However, they have decided it might be nice to have the information directly in Excel and the Excel export from Crystal 8.5 is not quite as accurate as we would like. So I am trying to figure out the best way to handle this with one statement without going through the trouble of automating Excel to pull the data. I was thinking of using MSQuery.

So can someone point me to a SQL statement that will pull the data so I only get the highest ID for each sales order number?

TIA, rasinc