Q:

Is it possible to get the latest value from every column in a one to many relationship?

Using this data set:
| RECID |  ID  | VALUE1 | VALUE2 | VALUE3 |                          RECDT |
----------------------------------------------------------------------------
|     1 | 1-01 |      1 |      2 |      3 | January, 01 2013 00:00:00+0000 |
|     2 | 1-01 |      3 | (null) | (null) | January, 02 2013 00:00:00+0000 |
|     3 | 1-01 | (null) | (null) |      1 | January, 03 2013 00:00:00+0000 |

Is it possible to return the following result with a simple query?
|  ID  | VALUE1 | VALUE2 | VALUE3 |
-----------------------------------
| 1-01 |      3 |      2 |      1 | 

The data set needs to return the latest value for each column based off of a date and PtID.  So, for example, if I was interested in all changes to a PTID until January, 02, 2013, the result would look like this:
|  ID  | VALUE1 | VALUE2 | VALUE3 |
-----------------------------------
| 1-01 |      3 |      2 |      3 |

The schema has been started on sqlfiddle for anyone interested.

A:

Here is an approach that would work in many dialects of SQL:
select ptid,
       (select value1 from t t2 where t2.ptid = t.ptid and value1 is not null order by recdt desc limit 1
       ) as Value1,
       (select value2 from t t2 where t2.ptid = t.ptid and value2 is not null order by recdt desc limit 1
       ) as Value2,
       (select value3 from t t2 where t2.ptid = t.ptid and value3 is not null order by recdt desc limit 1
       ) as Value3
from t
where ptid = '1-01'
group by ptid

Some databases might prefer top or recnum =  1 instead of limit.
In MySQL, you can also do:
select ptid,
       substring_index(group_concat(value1 order by recdt desc), ',', 1) as Value1,
       substring_index(group_concat(value2 order by recdt desc), ',', 1) as Value2,
       substring_index(group_concat(value3 order by recdt desc), ',', 1) as Value3
from t
group by ptid

This would have the side effect of turning the values into character strings.  You could cast back to your desired type.  Also, if the values might contain commas, then you would want to use a different separator.

