vmart_query_07.sql

-- vmart_query_07.sql
-- Multicolumn subquery
-- Which products have the highest cost,
-- grouped by category and department

SELECT product_description, sku_number, department_description
FROM public.product_dimension
WHERE (category_description, department_description, product_cost) IN (
   SELECT category_description, department_description,
   MAX(product_cost) FROM product_dimension
   GROUP BY category_description, department_description);

输出

product_description        |      sku_number       |   department_description
---------------------------+-----------------------+---------------------------------
 Brand #601 steak           | SKU-#601             | Meat
 Brand #649 brooms          | SKU-#649             | Cleaning supplies
 Brand #677 veal            | SKU-#677             | Meat
 Brand #1371 memory card    | SKU-#1371            | Photography
 Brand #1761 catfish        | SKU-#1761            | Seafood
 Brand #1810 frozen pizza   | SKU-#1810            | Frozen Goods
 Brand #1979 canned peaches | SKU-#1979            | Canned Goods
 Brand #2097 apples         | SKU-#2097            | Produce
 Brand #2287 lens cap       | SKU-#2287            | Photography
...