Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ibYdt-0004yk-FB for pgsql-sql@arkaria.postgresql.org; Sun, 01 Dec 2019 23:28:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ibYds-0005DI-0e for pgsql-sql@arkaria.postgresql.org; Sun, 01 Dec 2019 23:28:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ibYdr-0005AA-Gw for pgsql-sql@lists.postgresql.org; Sun, 01 Dec 2019 23:28:39 +0000 Received: from mail-pl1-x62d.google.com ([2607:f8b0:4864:20::62d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ibYdk-0003wj-IA for pgsql-sql@lists.postgresql.org; Sun, 01 Dec 2019 23:28:37 +0000 Received: by mail-pl1-x62d.google.com with SMTP id az9so15401319plb.11 for ; Sun, 01 Dec 2019 15:28:32 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=kuUdwJvR2lvqbQy+KgA/eQITBBDao+JXBCHVmdycSZw=; b=lySv3JycBfUiC9Mb7Z0Oz0+Z94sKuQpgt8IZU2rVsZ0ULW4VNZrNIsBD6gcR0M6wXW QlDn6X9gzTeVEVSBp2Tt/p1StkO4FcFm8EixagKU2NeAzqEGExBlI/FKtsXEisy1D7nD G8ZaEFzChM8v4AvjRb2d/68ffhM4a0WuJU/hHc+Gq78rH3voY9I3yvGfMcLttkGpeesT 1R93MNdrSb6pRLL8GE47QvnwrMQn6o/srojHH8sZEwgW+Ht0uoXY4iYVdNOTtdpVmN3z muoyk0oj1t00D4kvHGJhFSbDXUZ1ORi7R0tvwD089N2lvZAM0PN/T1B6BYaV9OKuMko4 GjAQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=kuUdwJvR2lvqbQy+KgA/eQITBBDao+JXBCHVmdycSZw=; b=W51H2aPbh2aAeq4ovct1VRMgQ1+XB9CI1y9Z6fC4uODAxjLarzpTfh/bE6xllqAdRb yEwDMPKUKM7LKCA/DoPc3uDLcXieC3AqQ077q2+MyYsyb/QkPdvYCFYQ9JR5P4616Bhh O9eTCNiXClqQG/qgCRISnXm5BOUHRnB6WObNoDnaG6PLNHGzNSBk0m3QoJsK9UkBIv9i 11RJhdiyb7dzoybNLGEQktIVe4YpMHaDq9H02ilPKBx5xluyaEdYIkQmO4WrQ72mTGZW NG9DiBxg1vnsQ/IL6Cy9mPzz0AJW/4DbAeCXOIWLAVXs709Uah5F+u+uyfkSIFklpTtJ YTfQ== X-Gm-Message-State: APjAAAWxMJD2U4Kfxs/98za6c4LKs/pzHVvRsAUfqJkfsnol0nNUVx7H 35qqljW0nBtrHn7Axi+49vh1XXqC X-Google-Smtp-Source: APXvYqzWndjjEufUjyo0izAWNFV/wIPSGsYzTUyfQADsWHRF7lBy2ABn0LhIgmx74hpZr65F7t8h3g== X-Received: by 2002:a17:90a:274b:: with SMTP id o69mr26677269pje.88.1575242910779; Sun, 01 Dec 2019 15:28:30 -0800 (PST) Received: from [10.106.128.50] ([155.100.47.2]) by smtp.gmail.com with ESMTPSA id 1sm31283378pgp.88.2019.12.01.15.28.30 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Sun, 01 Dec 2019 15:28:30 -0800 (PST) From: Rob Sargent Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_A64F831C-D8F7-4191-9A80-43845D20848D" Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.11\)) Subject: Re: Solving my query needs with Rank and may be CrossTab Date: Sun, 1 Dec 2019 16:28:29 -0700 In-Reply-To: Cc: pgsql-sql@lists.postgresql.org To: Iaam Onkara References: X-Mailer: Apple Mail (2.3445.104.11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --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 wrote: >=20 > Hi Friends, >=20 > 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 = >=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 = =20 >=20 > But the result set that I want = 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

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 =3D '8480-6' or = v.code=3D'8462-4' or v.code=3D'39156-5' or v.code=3D'8302-2')
)
SELECT c.display, uom, val, created_on
from v_max v inner join codes c on v.code=3Dc.code
where r =3D 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


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