(Query SQL) Limit the number of rows in a select with Inner Join and Where in

Asked

Viewed 354 times

1

I have 3 tables, products, Category, product_categories, this last one was created pq the product and category relation is n to n. I have a select that returns me all products of all categories, but I wanted to return me only 10 products of each category

This is my select:

select "products".*, "product_categories"."idCategory" as "idCategory" from "products" inner join "product_categories" on "products"."id" = "product_categories"."idProduct" where "product_categories"."idCategory" in (5,4,10,7)

If the limit placed at the end of the query is returned only the first 10 products and their respective categories

And I’ve also thought about doing several consultations, one for each category, but that would be very long compared to what I have

sql postgresql

2 answers

4


It is possible to use the ROW_NUMBER to establish an index for the category thus with the PATITION BY, every time it is a new category, it restarts the index.

Put this as a subselect and on WHERE of the outside query put filtering the quantity.

SELECT *
FROM
(
    SELECT 
    ROW_NUMBER() OVER(PARTITION BY "PRODUCT_CATEGORIES"."IDCATEGORY" ORDER BY "PRODUCT_CATEGORIES"."IDCATEGORY") AS INDICE,
    "PRODUCTS".*, 
    "PRODUCT_CATEGORIES"."IDCATEGORY" AS "IDCATEGORY" 
    FROM "PRODUCTS"
    INNER JOIN "PRODUCT_CATEGORIES" ON "PRODUCTS"."ID" = "PRODUCT_CATEGORIES"."IDPRODUCT" 
    WHERE "PRODUCT_CATEGORIES"."IDCATEGORY" IN (5,4,10,7)
) AS X
WHERE X.INDICE <= 10

  • Just out of curiosity, how would this be for MS SQL SERVER?

  • 1

    Something like this: CREATE TABLE TEST ( ID VARCHAR(100), PRODUCT VARCHAR(100), VARCHAR(100) ) INSERT INTO TEST VALUES (1, 'APPLE', 'FRUIT') INSERT INTO TEST VALUES (2, 'PEAR', 'FRUIT') INSERT INTO TEST VALUES (3, 'CARROT', 'VEG') INSERT INTO TEST VALUES (4, 'BEET', 'VEG') INSERT INTO TEST VALUES (5, 'BANANA', 'FRUIT') INSERT INTO TEST VALUES (6, 'ABOBORA', 'VEG') SELECT CATEGORIA, PRODUCT FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY CATEGORY ORDER BY CATEGORY) AS QTD, PRODUCT, CATEGORY FROM TEST GROUP BY CATEGORIA, PRODUCT ) AS X WHERE QTD <= 2

  • Interesting, thank you very much.

  • I put it on JS Fidle to make it formatted: https://jsfiddle.net/n253oLda/

  • I’ve tested it here in a local bank, it works fine, I’ve never needed anything like it, but if I ever need it, I’ll thank you

  • It worked, thank you very much!!! And the best thing is that this query takes as long as the one I was using, very good

Show 1 more comment

1

It compromises the request time a little, but I think it solves your problem by using a subquery

select "products".*, "product_categories"."idCategory" as "idCategory" 
from "products" inner join "product_categories" 
on "products"."id" = 
    (SELECT "product_categories"."idProduct" 
    FROM 'product_categories' 
    where "product_categories"."idCategory" in (5,4,10,7) LIMIT 10)

  • just out of curiosity, how would it look for MS SQL SERVER?

  • 1

    I tried to use the code, but it gave error (ERROR: more than one Row returned by a subquery used as an Expression), only works if you put the limit as 1 and still returns several lines with the same product, then I believe that your query would not return me 10 products for each category

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.