Select MAX Date in Crystal Reports Using SQL

I'm very new to SQL, but have been using Crystal Reports for a while. I have the following table and need to marry the promise date (shown below) to the data in another table (NOT shown). The table below and the other table have a Many-to-Many Relationship and so I can't link them in Crystal directly. I also need to be able to group data by the PromiseDate (shown below) AND then group the report by other data for the other table.

Because I need groups based on data in both tables I haven't found any other Crystal Reports' techniques that will work (like using Shared Variables between the main report and a sub-report).

I need to be able to SELECT each Sales Order just once and return the Maximum Promise Date Listed in Crystal Reports. Once I have this Command created in Crystal Reports using a SQL statement I can then easily tie the two tables together.

Listed below is a sample of the table I'm trying to query via SQL in Crystal Reports Command, Also below is a sample of how I want the results of the Query to look:

SalesOrderNo ItemCode PromiseDate S34413 /C 8/1/2011 S34413 /C S34413 /C S34413 0901402 8/8/2011 S34413 /1PM 8/1/2011 S34413 /C S34413 /C S34932 /C 8/19/2011 S34932 /C 8/19/2011 S34932 /1 8/12/2011 S34932 /M95 8/12/2011 S34932 /C S34932 /C S34932 *592-EUTC 8/12/2011 S34932 /F 8/12/2011

The Data I need returned would look like this:

SalesOrderNo PromiseDate S34413 8/8/2011 S34932 8/19/2011