PostgreSQL – Alternating Rows and Restarting if Rows Remain

postgresql

I'm trying to select below table, grouped by two (=max, if there is only one, show one) of each supplier_id. But, after all suppliers are selected it needs to start over. So this is the table:

Table: products

ID   Supplier_id   Name            created_at
---------------------------------------------
1       1          productname     2014-01-12
2       3          productname     2014-01-12
3       2          productname     2014-01-18
4       2          productname     2014-01-18
5       3          productname     2014-01-17
6       1          productname     2014-01-17
7       2          productname     2014-01-16
8       4          productname     2014-01-10
9       4          productname     2014-01-14
10      2          productname     2014-01-17
11      5          productname     2014-01-13

After selecting the rows I want this result:

Table: products

ID   Supplier_id   Name             created_at
----------------------------------------------
1       1          productname      2014-01-12
6       1          productname      2014-01-17
3       2          productname      2014-01-18
4       2          productname      2014-01-18
2       3          productname      2014-01-12
5       3          productname      2014-01-17
8       4          productname      2014-01-10 
9       4          productname      2014-01-14
11      5          productname      2014-01-13
7       2          productname      2014-01-16
10      2          productname      2014-01-17

On https://stackoverflow.com/questions/15969614/in-sql-how-to-select-the-top-2-rows-for-each-group I almost found what I was looking for.

SELECT *
FROM   products as p
WHERE 
        (
            SELECT  COUNT(*) 
            FROM    products as pr
            WHERE p.supplier_id = pr.supplier_id  AND p.created_at >= pr.created_at
        ) <= 2
        order by p.supplier_id

But as I told, after 2 of each supplier_id's are selected it needs to start over if there are any rows left. Anyone who knows this is possible with a query?

Best Answer

I think you need something using ROW_NUMBER() and (integer) division by 2, along the lines of this:

WITH p AS
( SELECT *, ROW_NUMBER() OVER (PARTITION BY supplier_id ORDER BY id)-1 AS rn
  FROM products
)
SELECT * 
FROM p
ORDER BY rn/2, supplier_id, rn ;

Tested at SQLFiddle.

Related Question