Re: Solving my query needs with Rank and may be CrossTab

On Dec 1, 2019, at 3:54 PM, Iaam Onkara <iamonkara@gmail.com> wrote:

Hi Friends,

I have a table with data like this gist https://gist.github.com/daya/d0794efcd4278fc5dce6e7339d03a8fd and I want to fetch the latest values for a given set of attributes so the result set looks like this gist https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6

Please note in the desired result set 

1. There is an assumed mapping of Code to display i.e. code 39156-5 is BMI.
2. "Oxygen Saturation" has only one value and "Pulse" has no value

In my attempts and with some help I have  this query 

with v_max as
(SELECT
code, uom, val, created_on, dense_rank() over ( partition by code order by created_on desc) as r
FROM vitals v
where (v.code = '8480-6' or v.code='8462-4' or v.code='39156-5' or v.code='8302-2')
)
SELECT c.display, uom, val, created_on
from v_max v inner join codes c on v.code=c.code
where r = 1; 

which gives this result 

But the result set that I want I am unable to get. Or if is it even possible to get?

Thanks for your help