[SQL] Find the Nth group (DENSE_RANK / OVER / SUBQUERY)

<1> 

-- Mainquery : SLECT shipment_id, total_weight FROM (subquery) subquery_name WHERE rank=3
-- Subquery : SELECT shipment_id, a, b FROM amazon_shipment GROUP BY shipment_id 

SELECT shipment_id, total_weight, heavy_num
FROM(
    SELECT shipment_id, 
        SUM(weight) AS total_weight,
        DENSE_RANK() OVER (ORDER BY SUM(weight) DESC) AS heavy_num
    FROM amazon_shipment
    GROUP BY shipment_id) ranked_shipment
WHERE heavy_num=3;

 

* DENSE_RANK() OVER(_________) 

      1) ORDER BY : (essential) 순위를 매길 때

      2) PARTITION BY : (option) 그룹별로 순위를 나누고 싶을 때

 

*Query Order

     SUB Queries > MAIN Queries

     FROM > WHERE > HAVING > SELECT > ORDER BY > LIMIT