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

> On Dec 1, 2019, at 3:54 PM, Iaam Onkara <iamonkara(at)gmail(dot)com> wrote:
>
> Hi Friends,
>
> I have a table with data like this gist https://gist.github.com/daya/d0794efcd4278fc5dce6e7339d03a8fd <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 <https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6>
>
> Please note in the desired result set
> There is an assumed mapping of Code to display i.e. code 39156-5 is BMI.
> "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 <https://gist.github.com/daya/19ca22a837ed9998c117f38ff3cce3f2>
>
> But the result set that I want <https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6> I am unable to get. Or if is it even possible to get?
>
> Thanks for your help
>

I take it the last value by timestamp per code per patient is the one to be reported? Or is there a time window?
Turning rows into columns can be done with sub-selects per derived column or (usually faster) temporary tables built up in separate selects with each adding usually one column (but possibly more).