CODEWITHSUNDEEP
sqlsql-server-2008-r2
afuzzyllama
afuzzyllama

Reputation: 6548

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.

Upvotes: 2

Views: 96

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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.

Upvotes: 2

Jakub Kania
Jakub Kania

Reputation: 16487

Here is an aproach that would work in many dialects of SQL without subqueries and producing only one row of output.

SELECT f1.value1,f3.value2,f5.value3 FROM FUNTIMES f1 
LEFT JOIN funtimes f2 ON
f1.recdt<f2.recdt AND f2.value1 IS NOT NULL
JOIN funtimes f3 ON f1.ptid=f3.ptid
LEFT JOIN funtimes f4 ON
f3.recdt<f4.recdt AND f4.value2 IS NOT NULL
JOIN funtimes f5 ON f1.ptid=f5.ptid
LEFT JOIN funtimes f6 ON
f5.recdt<f6.recdt AND f6.value3 IS NOT NULL
WHERE f1.value1 IS NOT NULL AND f2.value1 IS NULL
AND f3.value2 IS NOT NULL AND f4.value2 IS NULL
AND f5.value3 IS NOT NULL AND f6.value3 IS NULL;

Compare to the one above: http://www.sqlfiddle.com/#!2/9ee9a/34

Upvotes: 0

Related Questions