Select Max values from queries for multiple Schedule_Number

[mcolson]

I am trying to select the max for each Schedule_Number when Process
Description = 'Exit Cold Rinse'. In the following table, 2:00 and
4:00 should be returned for 12345_001 and 12345_002 respectively. I
have tried to join the two queries and would like to use the current
Schedule_Number as one of the criteria when determining the max.
Below is some code that I've used thus far? Does anyone have
suggestions?

*Schedule_Number * Process_Description * TMDT
*12345_001 * Exit Cold Rinse * 1/07/08 01:00:00 PM
*12345_001 * Enter Cold Rinse * 1/07/08 01:30:00 PM
*12345_001 * Exit Cold Rinse * 1/07/08 02:00:00 PM
*12345_002 * Enter Cold Rinse * 1/07/08 02:30:00 PM
*12345_002 * Exit Cold Rinse * 1/07/08 03:00:00 PM
*12345_002 * Enter Cold Rinse * 1/07/08 03:30:00 PM
*12345_002 * Exit Cold Rinse * 1/07/08 04:00:00 PM

Select *
From
(Select distinct Schedule_Number
From dbo.Process_Data
WHERE left(Schedule_Number,5) = '12345') as Query1
left join
(Select *
From dbo.Process_Data
Where TMDT =
(SELECT Max(TMDT)
FROM dbo.Process_Data
WHERE Process_Description = 'Exit Cold Rinse' and
Query1.Schedule_Number = Query2.Schedule_Number)) as Query2
on Query1.Schedule_Number=Query2.Schedule_Number

[Plamen Ratchev]

Here is one way to get you the results:

SELECT Schedule_Number,
Process_Description,
MAX(TMDT) AS TMDT
FROM Process_Data
WHERE Process_Description = 'Exit Cold Rinse'
AND Schedule_Number LIKE '12345%'
GROUP BY Schedule_Number, Process_Description

Really no need for joins and the logic for Schedule_Number is converted to
utilize any index on the column.

HTH,

Plamen Ratchev
http://www.SQLStudio.com