Clever Geek Handbook

Order by value of an arbitrary attribute in hstore

I have entries like these:

id, hstore_col
1,  {a: 1, b: 2}
2,  {c: 3, d: 4}
3,  {e: 1, f: 5}

How do I order them by max / min value inside hstore for any attribute ?

The result should be like this (order below):

id, hstore_col
1,  {a: 1, b: 2}
3,  {e: 1, f: 5}
2,  {c: 3, d: 4}

I know I can only order them by a specific attribute like this: my_table.hstore_fields -> 'a'

but it doesn't work for my problem.

+3


source to share


3 answers


Convert to array using avals

and move the resulting array from text to int. Then sort the array and order the results by the 1st element of the sorted array.

select * from mytable
order by (sort(avals(attributes)::int[]))[1]


http://sqlfiddle.com/#!15/84f31/5

+4


source


If you know all the elements, you can simply put them all together like this:

ORDER BY greatest(my_table.hstore_fields -> 'a', my_table.hstore_fields -> 'b',my_table.hstore_fields -> 'c', my_table.hstore_fields -> 'd', my_table.hstore_fields -> 'e', my_table.hstore_fields -> 'f')


or

ORDER BY least(my_table.hstore_fields -> 'a', my_table.hstore_fields -> 'b',my_table.hstore_fields -> 'c', my_table.hstore_fields -> 'd', my_table.hstore_fields -> 'e', my_table.hstore_fields -> 'f')
+2


source


Using svals

, you can create an exploded version of the values hstore_col

- then you can sort by those values ​​and get the first record from each of them. Undoubtedly a much more efficient way to do it, but here's the first pass:

select my_table.id, my_table.hstore_col
from my_table
join (
    select id, svals(hstore_col) as hstore_val
    from my_table
) exploded_table
    on my_table.id = exploded_table.id
group by my_table.id, my_table.hstore_col
order by my_table.id, exploded_table.hstore_val desc
0


source






More articles:


All Articles