SQL: grouping by maximum date and ID

Another reminder to myself. I have an audit table that is populated by a trigger on a table. I use this table to drive another process and that process involves finding the most recent addition to the table for a specific work order. So basically if a work order has it’s status changed multiple times—which is perfectly valid—I need to know the last change only. Here is what the source data looks like after changing status on a couple of work orders:

Id	WorkorderId	OldStatus	NewStatus	TimeOfUpdate
F0FE4F81-4CEF-DE11-8442-001C23437026	10	null	CLOSED	2009-12-22 22:51:14.377
F1FE4F81-4CEF-DE11-8442-001C23437026	10	CLOSED	OPEN	2009-12-22 22:51:14.390
F2FE4F81-4CEF-DE11-8442-001C23437026	10	OPEN	CLOSED	2009-12-22 22:51:14.390
F3FE4F81-4CEF-DE11-8442-001C23437026	9	null	CLOSED	2009-12-22 22:51:14.390

Because I created these by firing the trigger in a SQL script for testing the times for two of the records for work order 10 are identical. That’s OK; in fact I need to deal with this possibility in my application so I’m glad it happened that way. Anyway, here’s the SQL that I wrote to get the information that I need. Not rocket science but it is just one of those things that had me stumped for a while until it dawned on me that I needed an inner join rather than a subquery.

select w.Id
 , w.NewStatus
 , w.OldStatus
 , w.TimeOfUpdate
 , w.WorkorderId
from Test.WCS.WorkorderStatusChange w
 -- Inner join on the max date grouped by workorder id
 inner join (select w.WorkorderId as woi, MAX(w.TimeOfUpdate) as tou
   from Test.WCS.WorkorderStatusChange w
   group by w.WorkorderId) as t
  on t.tou = w.TimeOfUpdate
  and t.woi = w.WorkorderId

And that gives me what I need. Again, notice that the two results for work order 10 are valid because both have the MAX(TimeOfUpdate) value:

Id	NewStatus	OldStatus	TimeOfUpdate	WorkorderId
F3FE4F81-4CEF-DE11-8442-001C23437026	CLOSED	null	2009-12-22 22:51:14.390	9
F1FE4F81-4CEF-DE11-8442-001C23437026	OPEN	CLOSED	2009-12-22 22:51:14.390	10
F2FE4F81-4CEF-DE11-8442-001C23437026	CLOSED	OPEN	2009-12-22 22:51:14.390	10

Of course the real question on my mind now is how the heck to do that with HQL or the Criteria API in NHibernate? I’ve wrestled with it for some time now and have basically given up. I am going to grab the whole lot of data from SQL Server and use LINQ to objects to filter out what I need. This is acceptable in this particular case because I delete the rows in the status table once I have read them in; they are only there long enough for the application to add a message to a message queue using the information. Still, it’s bugging me that I cannot figure out the query in NHibernate.