PostgreSQL - Order By using Arrays. Can I create array dynamically?

Question

I'm using PostgreSQL 9.1.13

Here is what I've done so far:

/* First Array ==> SELECT raggruppamento FROM ok_filiali GROUP BY raggruppamento ORDER by count(raggruppamento) DESC */
/* Second Array ==> SELECT banker FROM ok_filiali GROUP BY banker ORDER by count(banker) DESC */

SELECT mandato, raggruppamento, banker
FROM ok_filiali
ORDER BY
idx(array['MI','FI','BS','RM','BO','TO','GE','*','PD','PR'], ok_filiali.raggruppamento), 
idx(array['120','MF','28','921','30','29','56','38','76','33', '27', '8037', '5038', '173', '5984', '45', '104', '46', 
    '84', '284', '176', '5311', '209', '248', '5138', '5064', '325', 
    '171', '348', '255', '274', '373', '5915', '318', '5076'], ok_filiali.banker)

For now I've used the above queries and created manually the 2 arrays.

What I need to do is to dynamically create the 2 arrays used for the Order By (using directly the 2 queries)

Is it possible to do that?

Thanks

Answer 1

If I understand correctly your goal is to sort your result based on how often certain values occur. This is easier to accomplish with window functions

SELECT mandato, raggruppamento, banker, 
    COUNT(*) OVER (PARTITION BY raggruppamento) AS order1.
    COUNT(*) OVER (PARTITION BY banker) AS order2
FROM ok_filiali
ORDER BY order1 DESC, order2 DESC;