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

--000000000000c411330598acf522
Content-Type: text/plain; charset="UTF-8"

@Rob: There is no time window. It is the latest values for given set of
attributes regardless of timestamp. If some attributes have multiple values
then multiple rows can be returned with other attributes having blank
values.

Creating one sub select for one column is an obvious approach but will not
be performant specially when the dataset grows, I am looking for a solution
which doesn't require one sub select per column.

On Sun, Dec 1, 2019 at 5:28 PM Rob Sargent <[email protected]> wrote:

>
>
> On Dec 1, 2019, at 3:54 PM, Iaam Onkara <[email protected]> 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
> <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).
>
>

--000000000000c411330598acf522
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">@Rob: There is no time window. It is the latest values for=
 given set of attributes regardless of timestamp. If some attributes have m=
ultiple values then multiple rows can be returned with other attributes hav=
ing blank values.<div><br></div><div>Creating=C2=A0one sub select for one c=
olumn is an obvious approach but will not be performant specially when the =
dataset grows, I am looking for a solution which doesn&#39;t require one su=
b select per column.<br></div></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Sun, Dec 1, 2019 at 5:28 PM Rob Sargent &l=
t;<a href=3D"mailto:[email protected]">[email protected]</a>&gt; wr=
ote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div style=
=3D"overflow-wrap: break-word;"><br><div><br><blockquote type=3D"cite"><div=
>On Dec 1, 2019, at 3:54 PM, Iaam Onkara &lt;<a href=3D"mailto:iamonkara@gm=
ail.com" target=3D"_blank">[email protected]</a>&gt; wrote:</div><br><div=
><div dir=3D"ltr">Hi Friends,<div><br></div><div>I have a table with data l=
ike this gist <a href=3D"https://gist.github.com/daya/d0794efcd4278fc5dce6e=
7339d03a8fd" target=3D"_blank">https://gist.github.com/daya/d0794efcd4278fc=
5dce6e7339d03a8fd</a> and I want to fetch the latest values for a given set=
 of attributes so the result set looks like this gist=C2=A0<a href=3D"https=
://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6" target=3D"_blank"=
>https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6</a></div><di=
v><br></div><div>Please note in the desired result set=C2=A0</div><div><ol>=
<li>There is an assumed mapping of Code to display i.e. code=C2=A039156-5 i=
s BMI.</li><li>&quot;Oxygen Saturation&quot; has only one value and &quot;P=
ulse&quot; has no value</li></ol><div>In my attempts and with some help I h=
ave=C2=A0 this query=C2=A0</div></div><div><br></div><div><font face=3D"mon=
ospace">with v_max as <br>(SELECT <br>	code, uom, val, created_on, dense_ra=
nk() over ( partition by code order by created_on desc) as r <br>FROM vital=
s v<br>where (v.code =3D &#39;8480-6&#39; or v.code=3D&#39;8462-4&#39; or v=
.code=3D&#39;39156-5&#39; or v.code=3D&#39;8302-2&#39;)<br>) <br>SELECT c.d=
isplay, uom, val, created_on <br>from v_max v inner join codes c on v.code=
=3Dc.code<br>where r =3D 1;=C2=A0<br></font></div><div><font face=3D"monosp=
ace"><br></font></div>which gives <a href=3D"https://gist.github.com/daya/1=
9ca22a837ed9998c117f38ff3cce3f2" target=3D"_blank">this result</a>=C2=A0<di=
v><br></div><div>But the <a href=3D"https://gist.github.com/daya/0cb7f86825=
20a1dd4cdda8c0266f77f6" target=3D"_blank">result set that I want</a>=C2=A0I=
 am unable to get. Or if is it even possible to get?</div><div><br></div><d=
iv>Thanks for your help</div><div><br></div></div>
</div></blockquote></div><br><div>I take it the last value by timestamp per=
 code per patient is the one to be reported? Or is there a time window?</di=
v><div>Turning rows into columns can be done with sub-selects per derived c=
olumn or (usually faster) temporary tables built up in separate selects wit=
h each adding usually one column (but possibly more).</div><div><br></div><=
/div></blockquote></div>

--000000000000c411330598acf522--