Webmaster Forum / ColdFusion / Advanced Techniques / April 2006

CF 7, SQL Server 2005
I will try and explain this as clearly as possible, let me know if I failed
and I will attempt to clear it up.

The application keeps track of orders as they go through phases of production.
Lets assume there are two phases of production, phase1 and phase2 with 3
statuses in each phase, new, open, complete. (depending on the phase an order
can be in more than one phase at once.)

There is a phase_log table which is a reference table that has a row  for
every time the order's status changes.
Lets say the row in the phase_log table looks like this:

phase_logID  |  OrderID  |  PhaseID |  StatusID  | etc...

The main page will have a table for each phase, showing a list of the orders
that currently have  rows in the phase_log table for that phase. The kicker is,
I only want to pull one row (the latest status change which will consequently
be the highest status ID) for each order per phase.

once I pull the data I guess I can populate a struct or array, but I am new to
this and I can't figure out how to get the query/s I need to get it.

Any help is appreciated,
Thanks