How to use subqueries to find both the minimum and maximum value in SQLite-CodePudding

I am trying to use subqueries in SQLite to find the minimum and maximum values of my data and return the product name of this min and max. The following lines show two separate queries that will give me the answer, but I would like to combine them like I tried in the third query. However, no results come out of my third query.

-- Select minimum and maximum price
SELECT MIN(UnitPrice), MAX(UnitPrice)
FROM Products;

-- Return productnames
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice = 296.5 OR UnitPrice = 2.5;

-- Combine first 2 queries by using subquery gives no results
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice = (
    SELECT MAX(UnitPrice) OR MIN(UnitPrice)
    FROM Products 
);

I tried several ways of combining the queries, but they all give errors or show no results. Does anyone has any tips? (The assignment is to make use of subqueries, so they would need to be in there)

CodePudding user response：

If you wanted to continue with your subquery approach, you could use:

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice = (SELECT MIN(UnitPrice) FROM Products) OR
      UnitPrice = (SELECT MAX(UnitPrice) FROM Products);

You could also use the RANK() analytic function here:

WITH cte AS (
    SELECT *, RANK() OVER (ORDER BY UnitPrice) rnk1,
              RANK() OVER (ORDER BY UnitPrice DESC) rnk2
    FROM Products
)

SELECT ProductName, UnitPrice
FROM cte
WHERE rnk1 = 1 OR rnk2 = 1;