Foamee Beer or Coffee for some help

["ProseDoctor" <webforumsuser@xxxxxxxxxxxxxx>]

Hi,

 I'm hoping someone can steer me in the right direction on writing an SQL 
query. Sorry if this gets a little long but I want to provide enough info to 
get the help I need.

 Here's the skinny:

 I've written a CRM system which allow users to maintain their list of clients 
(names & mailing address etc.) Every 2 months the users (Consultants) can login 
and order/send their customers (Customers) personalized direct mail brochures 
where the brochures get a personal message printed on them from the sender.

 I've got a MySQL database that has 4 tables.

 Table 1: Consultants - the registered users
 Table 2: Customers - the clients of the registered users
 Table 3: Messages
 Table 4: ConsultantOrders

 The problem is that a Consultant can place more than one Order during each 
cycle.

 So what I want to be able to do is query the DB, find all consultants who have 
placed an order - but only pull THE MOST RECENT Order as the newOrder - and 
then extract all the Consultants' Clients to compile a master mailing file.

 What I do right now is first run a query to find which consultants have more 
than one order in the current cycle. Then I manually go in and delete the 
duplicate orders. Once that's done I then run this query:

 SELECT DISTINCT
 `ConsultantOrders`.`ConsultantID`,
 `ConsultantOrders`.`OrderID`,
 `Consultants`.`ConsultantID`,
 `Consultants`.`FirstName`,
 `Consultants`.`LastName`,
 `Consultants`.`Address1`,
 `Consultants`.`Address2`,
 `Consultants`.`City`,
 `Consultants`.`State`,
 `Consultants`.`Zip`,
 `Consultants`.`Phone`,
 `Consultants`.`SecondaryPhone`,
 `Consultants`.`EmailAddress`,
 `Consultants`.`Website`,
 `Consultants`.`MessageID`,
 `Consultants`.`Allowed`,
 `Customers`.`FirstName`,
 `Customers`.`LastName`,
 `Customers`.`Address1`,
 `Customers`.`Address2`,
 `Customers`.`City`,
 `Customers`.`State`,
 `Customers`.`Zip`,
 `Customers`.`Language`,
 `Customers`.`Mail`,
 `Customers`.`ConsultantID`,
 `Messages`.`MessageID`,
 `Messages`.`Message`
 FROM
 `ConsultantOrders` ,
 `Consultants`
 Inner Join `Customers` ON '' = '' ,
 `Messages`
 WHERE
 `Consultants`.`ConsultantID` =  `ConsultantOrders`.`ConsultantID` AND
 `Customers`.`ConsultantID` =  `ConsultantOrders`.`ConsultantID` AND
 `Customers`.`Mail` =  '1' AND
 `Consultants`.`MessageID` =  `Messages`.`MessageID` AND
 `Customers`.`Status` = '1' AND
 `Consultants`.`Allowed` = '1' AND
 `ConsultantOrders`.`OrderID` > 2512

 So can someone tell me how I'd write a direct query to not have to manually 
delete the duplicate orders?

 I think I need to be using a (Max) function but for the life of me I can't 
seem to get it to work right.

 Any help would be greatly appreciated. Beer or Coffee via foamee.com as a 
reward?

 Cheers,

 Eric