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);
Output
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 ...