Get top rows by "category" from a collection

Question

I am using Couchbase and N1QL.

Let's say I have a table of products. Products have a categoryId and a price among other attributes:

id	name	categoryId	price
1	product 1	cat1	5
2	product 2	cat2	100
3	product 3	cat1	25

I know how to get the 5 most expensive products for a single category. I would do something like this:

SELECT * FROM products
WHERE categoryId = 'cat1'
ORDER BY price DESC
LIMIT 5

But how would I query for the top 5 most expensive products per category? I know I can do a WHERE categoryId IN ['cat1', 'cat2'] but this would result in the top5 products among those categories, and not per category.

Any couchbase or N1QL experts that can help me out?

Thanks in advance!

Answer 1

Use WINDOW functions

SELECT p1.*, MISSING AS r
FROM (SELECT p.*, 
      RANK() OVER (PARTITION BY p.categoryId ORDER BY p.price DESC) AS r
      FROM products AS p
      WHERE p.categoryId IS NOT NULL) AS p1
WHERE p1.r < 6;

Non-Window functions (UNNEST should perform better)

CREATE INDEX ix1 ON products(categoryId, price DESC);

SELECT p2.*, MISSING AS r
FROM (SELECT p.*,
      (SELECT RAW 1+COUNT(1)
       FROM products AS p1
       WHERE p1.categoryId = p.categoryId AND p1.price > p.price)[0] AS r
      FROM products AS p
      WHERE p.categoryId IS NOT NULL) AS p2
WHERE p2.r < 6;

OR

SELECT u.*
FROM (SELECT DISTINCT RAW p.categoryId
      FROM products AS p
      WHERE p.categoryId IS NOT NULL) AS c
UNNEST (SELECT p1.*
    FROM products AS p1
    WHERE p1.categoryId = c
    ORDER BY p1.categoryId, p1.price DESC
    LIMIT 5) AS u;

OR

WITH categoryIds AS (SELECT DISTINCT RAW p.categoryId
                     FROM products AS p
                     WHERE p.categoryId IS NOT NULL)
SELECT c AS categoryId,
       (SELECT p1.*
        FROM products AS p1
        WHERE p1.categoryId = c
        ORDER BY p1.categoryId, p1.price DESC
        LIMIT 5) AS top5
FROM categoryIds AS c ;