How to write SQL query to put specific results on each n-th position in PostgreSQL

user_4753915 0
Viewed 62

Let's imagine I have a table of products looking like this:

id | product_name
1 | Product 1
2 | Product 2
3 | Product 3
4 | Product 4
5 | Product 5
6 | Product 6
...

Is it possible to select all these records but, for example, I want Product 5 to appear at the 3rd position in the result set so that it would look like this:

1 | Product 1
2 | Product 2
5 | Product 5
3 | Product 3
4 | Product 4
6 | Product 6

EDIT: added real issue explanation

I am asking this because I have a big table of products (100 000+ records) and I need to make query that makes few certain products (3 of them with IDs X,Y,Z) get displayed at every 4th position of my result set so that my final result looks like:

1 | Product 1
2 | Product 2
3 | Product 3
X | Product X
4 | Product 4
5 | Product 5
6 | Product 6
Y | Product Y
7 | Product 7
8 | Product 8
9 | Product 9
Z | Product Z
10 | Product 10
...
postgresql sql
1 Answers

You can do this using window functions. The idea is to add a "grouping" to each set of rows. For the ids you care about, this is simply row_number(). The rest requires arithmetic.

You can do all of this in the order by clause:

order by (case when id in (x, y, z)
               then row_number() over (partition by (id in (x, y, z)) order by id)
               else ceiling(row_number() over (partition by (id in (x, y, z)) order by id) / 3.0)
          end),
         (id in (x, y, z)), id

Note that the groups have a size of 3 for the rest of the ids (there are 3 of them and then one special id). The second order by key puts the special ids at the end of each group.

user_11440350
Related