Re: Solving my query needs with Rank and may be CrossTab
Rob Sargent <[email protected]> Sun, 1 Dec 2019 16:28:29 -0700
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail=_A64F831C-D8F7-4191-9A80-43845D20848D Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On Dec 1, 2019, at 3:54 PM, Iaam Onkara <[email protected]> wrote: >=20 > Hi Friends, >=20 > 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> >=20 > Please note in the desired result set=20 > 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=20 >=20 > with v_max as=20 > (SELECT=20 > code, uom, val, created_on, dense_rank() over ( partition by code = order by created_on desc) as r=20 > FROM vitals v > where (v.code =3D '8480-6' or v.code=3D'8462-4' or v.code=3D'39156-5' = or v.code=3D'8302-2') > )=20 > SELECT c.display, uom, val, created_on=20 > from v_max v inner join codes c on v.code=3Dc.code > where r =3D 1;=20 >=20 > which gives this result = <https://gist.github.com/daya/19ca22a837ed9998c117f38ff3cce3f2>=20 >=20 > 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? >=20 > Thanks for your help >=20 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). --Apple-Mail=_A64F831C-D8F7-4191-9A80-43845D20848D Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii <html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; = charset=3Dus-ascii"></head><body style=3D"word-wrap: break-word; = -webkit-nbsp-mode: space; line-break: after-white-space;" class=3D""><br = class=3D""><div><br class=3D""><blockquote type=3D"cite" class=3D""><div = class=3D"">On Dec 1, 2019, at 3:54 PM, Iaam Onkara <<a = href=3D"mailto:[email protected]" class=3D"">[email protected]</a>>= wrote:</div><br class=3D"Apple-interchange-newline"><div class=3D""><div = dir=3D"ltr" class=3D"">Hi Friends,<div class=3D""><br = class=3D""></div><div class=3D"">I have a table with data like this gist = <a href=3D"https://gist.github.com/daya/d0794efcd4278fc5dce6e7339d03a8fd" = class=3D"">https://gist.github.com/daya/d0794efcd4278fc5dce6e7339d03a8fd</= a> and I want to fetch the latest values for a given set of attributes = so the result set looks like this gist <a = href=3D"https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6" = class=3D"">https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6</= a></div><div class=3D""><br class=3D""></div><div class=3D"">Please note = in the desired result set </div><div class=3D""><ol class=3D""><li = class=3D"">There is an assumed mapping of Code to display i.e. = code 39156-5 is BMI.</li><li class=3D"">"Oxygen Saturation" has = only one value and "Pulse" has no value</li></ol><div class=3D"">In my = attempts and with some help I have this = query </div></div><div class=3D""><br class=3D""></div><div = class=3D""><font face=3D"monospace" class=3D"">with v_max as <br = class=3D"">(SELECT <br class=3D""> code, uom, val, created_on, = dense_rank() over ( partition by code order by created_on desc) as r <br = class=3D"">FROM vitals v<br class=3D"">where (v.code =3D '8480-6' or = v.code=3D'8462-4' or v.code=3D'39156-5' or v.code=3D'8302-2')<br = class=3D"">) <br class=3D"">SELECT c.display, uom, val, created_on <br = class=3D"">from v_max v inner join codes c on v.code=3Dc.code<br = class=3D"">where r =3D 1; <br class=3D""></font></div><div = class=3D""><font face=3D"monospace" class=3D""><br = class=3D""></font></div>which gives <a = href=3D"https://gist.github.com/daya/19ca22a837ed9998c117f38ff3cce3f2" = class=3D"">this result</a> <div class=3D""><br class=3D""></div><div = class=3D"">But the <a = href=3D"https://gist.github.com/daya/0cb7f8682520a1dd4cdda8c0266f77f6" = class=3D"">result set that I want</a> I am unable to get. Or if is = it even possible to get?</div><div class=3D""><br class=3D""></div><div = class=3D"">Thanks for your help</div><div class=3D""><br = class=3D""></div></div> </div></blockquote></div><br class=3D""><div class=3D"">I take it the = last value by timestamp per code per patient is the one to be reported? = Or is there a time window?</div><div class=3D"">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).</div><div class=3D""><br = class=3D""></div></body></html>= --Apple-Mail=_A64F831C-D8F7-4191-9A80-43845D20848D--