agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed
From: Rob Sargent <robjsargent@gmail.com>
To: Iaam Onkara <iamonkara@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Solving my query needs with Rank and may be CrossTab
Date: Sun, 1 Dec 2019 16:28:29 -0700
Message-ID: <CC0E128E-79ED-4AC0-9FD0-D6C0F12C9859@gmail.com> (raw)
In-Reply-To: <CAMz9UCZFptR4zWdGm0sx2q0_p3K5CMYrPwKPtNP-6Lf_o576Qw@mail.gmail.com>
References: <CAMz9UCZFptR4zWdGm0sx2q0_p3K5CMYrPwKPtNP-6Lf_o576Qw@mail.gmail.com>



> 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 <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).

view thread (8+ messages)  latest in thread

Message-ID: <CC0E128E-79ED-4AC0-9FD0-D6C0F12C9859@gmail.com>
Permalink:  ../CC0E128E-79ED-4AC0-9FD0-D6C0F12C9859@gmail.com/
Also on:    postgresql.org/message-id/CC0E128E-79ED-4AC0-9FD0-D6C0F12C9859@gmail.com

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: robjsargent@gmail.com, iamonkara@gmail.com, pgsql-sql@lists.postgresql.org
  Subject: Re: Solving my query needs with Rank and may be CrossTab
  In-Reply-To: <CC0E128E-79ED-4AC0-9FD0-D6C0F12C9859@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox