1

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?

2

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.

1
  • Very nice!! Thank you, it works like a charm! Jan 29 '15 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.