Postgres Pro
Downloads
Home   >   mailing lists

Solving my query needs with Rank and may be CrossTab - Mailing list pgsql-sql

From Iaam Onkara
Subject Solving my query needs with Rank and may be CrossTab
Date
Msg-id CAMz9UCZFptR4zWdGm0sx2q0_p3K5CMYrPwKPtNP-6Lf_o576Qw@mail.gmail.com
Whole thread Raw
Responses Re: Solving my query needs with Rank and may be CrossTab
List pgsql-sql
Tree view
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 

But the result set that I want I am unable to get. Or if is it even possible to get?

Thanks for your help

pgsql-sql by date:

Previous
From: Monalee Bhandge
Date:
Subject: How to use Array in Array of Json
Next
From: Rob Sargent
Date:
Subject: Re: Solving my query needs with Rank and may be CrossTab