CODEWITHSUNDEEP
sqlsql-servert-sql
Randy
Randy

Reputation: 13

RANK or ROWNUM or different?

I'm trying to find orders with multiple products and exclude orders with only one product.

My data is organized as follows:

ORDER_ID PRODUCT_GROUP_ID PRODUCT_ID PROD_RANK
3574 1038 460 1
3574 1038 461 2
3574 1038 462 3
3574 3748 750 1
1417 9113 175 1
2265 8202 472 1
2265 3203 475 1

I've tested trying to grab only order with multiple product by this basic rank SQL and it does what it should, but how do I exclude order id 1417 from the rank? Is there a better way to accomplish what I'm trying to accomplish?

Below is the SQL with the output. As you can see, the rank is doing it's job but now I need to exclude all orders with only one product_group_id (1417).

Thanks

WITH TESTRANK AS(
        SELECT
                ORDER_ID
                ,PRODUCT_GROUP_ID
                ,PRODUCT_ID
                ,RANK() OVER(PARTITION BY ORDER_ID, PRODUCT_GROUP_ID ORDER BY PRODUCT_ID) AS ROWNUM_RANK
 
        FROM ORDER_DETAIL
        WHERE
                ORDER_ID IN (3574, 1417, 2265)
        )
SELECT * FROM TESTRANK
--WHERE ROWNUM_RANK=1
ORDER_ID PRODUCT_GROUP_ID PRODUCT_ID PROD_RANK
3574 1038 460 1
3574 3748 750 1
1417 9113 175 1
2265 8202 472 1
2265 3203 475 1

Upvotes: 0

Views: 97

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

I would just use max() and min():

WITH TESTRANK AS (
        SELECT ORDER_ID, PRODUCT_GROUP_ID, PRODUCT_ID,
               RANK() OVER (PARTITION BY ORDER_ID, PRODUCT_GROUP_ID ORDER BY PRODUCT_ID) AS ROWNUM_RANK,
               MAX(PRODUCT_GROUP_ID) OVER (PARTITION BY ORDER_ID) as MAX_PGI,
               MIN(PRODUCT_GROUP_ID) OVER (PARTITION BY ORDER_ID) as MIN_PGI,
        FROM ORDER_DETAIL OD
        WHERE ORDER_ID IN (3574, 1417, 2265)
       )
SELECT *
FROM TESTRANK
WHERE MIN_PGI <> MAX_PGI;

Upvotes: 1

Dale K
Dale K

Reputation: 27225

I think you're looking for COUNT(DISTINCT PRODUCT_GROUP_ID) OVER (PARTITION BY ORDER_ID) which unfortunately isn't possible. But which can be mimicked using DENSE_RANK.

WITH TESTRANK AS (
    SELECT
        ORDER_ID
        , PRODUCT_GROUP_ID
        , PRODUCT_ID
        , RANK() OVER (PARTITION BY ORDER_ID, PRODUCT_GROUP_ID ORDER BY PRODUCT_ID) AS ROWNUM_RANK

        -- Count distinct window function
        , DENSE_RANK() OVER (PARTITION BY ORDER_ID ORDER BY PRODUCT_GROUP_ID ASC) +
            DENSE_RANK() OVER (PARTITION BY ORDER_ID ORDER BY PRODUCT_GROUP_ID DESC) - 1 AS WINDOW_COUNT
    FROM #ORDER_DETAIL
    WHERE ORDER_ID IN (3574, 1417, 2265)
)
SELECT *
FROM TESTRANK
WHERE ROWNUM_RANK = 1 and WINDOW_COUNT > 1;

Returns:

ORDER_ID PRODUCT_GROUP_ID PRODUCT_ID
2265 8202 472
2265 3203 475
3574 3748 750
3574 1038 460

Upvotes: 1

Related Questions