SQL - Select * from latest except unless = null then latest non null

I'm sure there are some SQL whizz kids around these parts somwhere.

Ref Book   Amount Run_time Comment
--- ------ ------ -------- -------
22  Book22 45     09:30    NULL
23  Book23 34     09:30    Comment
24  Book24 67     09:30    NULL
25  Book25 57     09:30    NULL
26  Book26 543    09:30    NULL
23  Book23 34     10:00    NULL
22  Book22 45     10:00    OK
24  Book24 67     10:00    NULL
25  Book25 57     10:00    NULL

Results:

23  Book23 34     10:00    Comment
22  Book22 45     10:00    OK
24  Book24 67     10:00    NULL
25  Book25 57     10:00    NULL

I have a table as attached. I want to select Ref, Book, Amount & Run_time from the latest run (10:00) only but I also want the latest available comment if there is one. So run 10:00 may not have a comment but the same ref, book etc will have a comment from a previous run. This must also be able to cope with multiple previous comments (i.e. 6am & 7am and 10am run have a comment but I only want the comment from the latest run where there is a comment. Must also be able to handle NO comment on current or any previous run in which case just returns NULL.

Hope this makes sense.

I got the first part 开发者_StackOverflow(selecting all fields from latest run using Max but couldn't integrate the latest available comment.

Any help, much appreciated.

---

Derive a list of Refs and their associated MAX(Run_Time) & MAX(Run_Time) for non-NULL Comment values from the original table. Next, join the original table to that list twice: once to get Ref, Book, Amount, Run_Time, and second time to get Comment:

SELECT
  r.Ref,
  r.Book,
  r.Amount,
  r.Run_Time,
  c.Comment
FROM (
  SELECT
    Ref,
    MAX(Run_Time) AS Run_Time,
    MAX(CASE WHEN Comment IS NOT NULL THEN Run_Time END) AS Comment_Time
  FROM tblTrades
) m
  INNER JOIN tblTrades r ON r.Ref = m.Ref AND r.Run_Time = m.Run_Time
  LEFT JOIN  tblTrades c ON c.Ref = m.Ref AND c.Run_Time = m.Comment_Time

---

Another approach which may work for you is to add a subquery in the select list:

SELECT
    REF, 
    Book, 
    Amount, 
    Run_Time, 
    (SELECT MAX(Comment) FROM tblTrades WHERE REF = t.Ref) as Comment
FROM tblTrades t 
    WHERE Run_time=(SELECT MAX(Run_time) FROM tblTrades WHERE REF = t.Ref)