Mehdi Monzavi Mehdi Monzavi - 1 year ago 103
SQL Question

how to get last value of any attribute in postgresql pivot table

I have a table like this

id u_id attr_key attr_value process_id insert_time
1 1 name john 1 1
2 1 family smith 1 2
3 2 job clerk 2 3
4 1 name sarah 3 4

I have to find two things:

  1. I have to create a view by tablefunc(crosstab) to fetch a group of data for any of u_id it's simple

  2. I have to find (realtime) last value of any key of any u_id (like Hbase database) so I don't have any good solution

this is what i need

id u_id attr_key attr_value
4 1 name sarah
2 1 family smith

Any idea or function?
(its possible to add a column in my data model )

Answer Source

The most efficient solution to problems in Postgres is to use distinct on ():

The following will retrieve that for a single u_id

select distinct on (attr_key) id, u_id, attr_key, attr_value
from the_table
where u_id = 1
order by attr_key, insert_time desc;

An index on (u_id, attr_key, insert_time) should help for performance

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download