Postgresql order records by string pattern

Question

Let's say we have products with title, position and source.

I need to order the products by string pattern 'basse'. After order products should look like:

id  title   source  position
9   prod 9  b   0
1   prod 1  a   0
13  prod 13 s   0
14  prod 14 s   1
5   prod 5  e   0
10  prod 10 b   1
2   prod 2  a   1
15  prod 15 s   2
16  prod 16 s   3
6   prod 6  e   1
11  prod 11 b   2
3   prod 3  a   2
7   prod 7  e   2
12  prod 12 b   3
4   prod 4  a   3
8   prod 8  e   3

klin · Accepted Answer · 2016-12-02 09:16:53Z

1

select id, title, source, position
from (
    select id, title, source, position, new_source,
        case new_source
            when 'b' then 0
            when 'a' then 1
            when 's' then 2
            when 't' then 3
            else 4
        end as pos
    from (
        select id, title, source, position,
            case 
                when source <> 's' then source
                when (row_number() over w % 2)::int::bool then 's'
                else 't'
            end as new_source
        from products
        window w as (partition by source order by position)
        ) s
    ) s
window w as (partition by new_source order by position)
order by (row_number() over w)* 5+ pos;

Test it here.

edited Dec 2, 2016 at 9:16

answered Dec 1, 2016 at 18:29

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Serg M Ten · Accepted Answer · 2016-12-01 15:49:22Z

0

You won't be able to achieve this just by sorting. Instead, create a PL/pgSQL function "basse()" which returns a setof record. Inside the function, declare four unbound arrays of product%rowtype for b,a,s,e and open a cursor to iterate over products. Put each read product in its corresponding array. Then intercalate the arrays returning one element from the proper array (if not empty). Finally call RETURN at the end of the function. For using the function, do SELECT * FROM basse()

answered Dec 1, 2016 at 15:49

Serg M Ten

5,6064 gold badges29 silver badges49 bronze badges

Collectives™ on Stack Overflow

Postgresql order records by string pattern

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related