Question
SQL (Postgres) Question: What is the name and unit price of the most and least expensive product sold by company? Use a sub-query. (2) My
SQL (Postgres) Question:
What is the name and unit price of the most and least expensive product sold by company? Use a sub-query. (2)
My current query is:
SELECT productname, unitprice From products WHERE MAX(unitprice) IN (SELECT productname, unitprice FROM products WHERE MIN(unitprice));
However, this does not work. How do I find the maximum and minimum of a table listing using a subquery in SQL?
The top of the table looks like this:
productid | productname | supplierid | categoryid | quantityperunit | unitprice | unitsinstock | unitsonorder | reorderlevel | discontinued -----------+----------------------------------+------------+------------+----------------------+-----------+--------------+--------------+--------------+-------------- 1 | Chai | 1 | 1 | 10 boxes x 20 bags | 18.00 | 39 | 0 | 10 | 0 2 | Chang | 1 | 1 | 24 - 12 oz bottles | 19.00 | 17 | 40 | 25 | 0 3 | Aniseed Syrup | 1 | 2 | 12 - 550 ml bottles | 10.00 | 13 | 70 | 25 | 0 4 | Chef Anton's Cajun Seasoning | 2 | 2 | 48 - 6 oz jars | 22.00 | 53 | 0 | 0 | 0 5 | Chef Anton's Gumbo Mix | 2 | 2 | 36 boxes | 21.35 | 0 | 0 | 0 | 1 6 | Grandma's Boysenberry Spread | 3 | 2 | 12 - 8 oz jars | 25.00 | 120 | 0 | 25 | 0 7 | Uncle Bob's Organic Dried Pears | 3 | 7 | 12 - 1 lb pkgs. | 30.00 | 15 | 0 | 10 | 0 8 | Northwoods Cranberry Sauce | 3 | 2 | 12 - 12 oz jars | 40.00 | 6 | 0 | 0 | 0
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started