Is it possible to perform a single SQL query which SELECTs two max/min values from different columns?

Question

I am getting started with SQLite. I have done a pair of tutorials that have given me a global view of SQL statements. I think I would be able to fulfill my task with several simple queries. However, and for academic reasons, I would like to combine them into a somewhat more complex query.

Let's take a look at my problem, let's say I have this table PRODUCTS:

+---------+-------+--------+-------------+
| Product |  Kcal | Price  | Supermarket |
+---------+-------+--------+-------------+
   milk      300     1.01     Carrefour
   eggs      75      1.50     Carrefour
   bread     400     1.99     Carrefour
   milk      150     1.20     Lidl
   eggs      50      2.00     Lidl
   bread     300     1.50     Lidl

And let's say I want this output:

+-------------+----------+---------+-----------+---------+
| Supermarket | Max Kcal | Product | Min Price | Product | 
+-------------+----------+---------+-----------+---------+
   Carrefour      400       bread      1.01        milk
   Lidl           300       bread      1.20        milk

My simple query to retrive the keys associated to a max/min value is:

sql = ('SELECT PRODUCT, Supermarket '
   'from PRODUCTS '
   'where KCAL = (SELECT MAX(KCAL) from PRODUCTS) ')

I have tried to use GROUP BY statement in order to retrieve all max "kcal" grouped by "supermarket".

sql = ('SELECT Supermarket, MAX(KCAL), Product '
   'from PRODUCTS '
   'group by Supermarket '
   'order by Supermarket ')

As well as all min "prices" grouped by "supermarket".

sql = ('SELECT Supermarket, MIN(Price), Product '
   'from PRODUCTS '
   'group by Supermarket '
   'order by Supermarket ')

I do not find any clues on how to combine, however, these two queries in a single one with the goal of acessing the database one single time and reduce the expended time.

Is it even possible? or am I forced to do several queries?

I might have over-simplified my problem, but I have a 500MB database with plenty of tables for which I need each time a max/min value sorted by a common key ("supermarket"). I would need a lot of queries that take several minutes each time...

I wonder if it does make a difference if Kcals and Prices are in different tables, I have not yet tried to combine queries from different tables (I go from simple to complex).

Thank YOU!

Have you tried joining your 2 result sets, i.e. the "max kcal" set and the "min price" set, on the `Supermarket` column? — unfinishedmonkey, Nov 28 '17 at 15:16
Let's say your `MAX(kcal)` for a given supermarket is 500, but there are 8 different products that have 500 kcals. What would you want in your output? — Aaron Dietz, Nov 28 '17 at 15:18

score 1 · Accepted Answer · edited Nov 29 '17 at 12:52

The idea is to create two tables.

First table(e.g table A) containing Supermarket, Max Kcal, Product

Second table(e.g table B) containing Supermarket, Min Price, Product

Then you can join both table into one table.This can be done in one query. See my answer here

The query would look like this:

SELECT A.supermarket, 
       A.max_kcal, 
       C.NAME, 
       B.min_price, 
       D.NAME 
FROM   (SELECT supermarket, 
               Max(kcal) AS max_kcal 
        FROM   product 
        GROUP  BY supermarket) AS A 
       JOIN (SELECT supermarket, 
                    Min(price) AS min_price 
             FROM   product 
             GROUP  BY supermarket) AS B 
         ON A.supermarket = B.supermarket 
       JOIN product C 
         ON C.kcal = A.max_kcal 
            AND C.supermarket = A.supermarket 
       JOIN product D 
         ON D.price = B.min_price 
            AND D.supermarket = B.supermarket
ORDER BY A.supermarket;

I use additional table C and D to get the product name. Hope this helps.

You only need one `ORDER BY` at the end. And be carefull this also have issue if you multiple element with equal max_KCal. — Juan Carlos Oropeza, Nov 29 '17 at 12:52

Juan Carlos Oropeza · Answer 2 · 2017-11-28T15:36:50.097

0

You need two query, but to get the max and min of a group you need be carefull. So I suggest you check the answers on this question: Also you need to solve what happen in case of Ties.

Get records with max value for each group of grouped SQL results

Now regarding your question if you have two queries based on supermarket you just need to join them

 SELECT Q1.Supermarket, Q1.`Max Kcal`, Q1.`Product`,
        Q2.`Min Price`, Q2.`Product`
 FROM ( Query1 ..) as Q1
 JOIN ( Query2 ..) as Q2
   ON Q1.Supermarket = Q2.Supermarket

edited Nov 28 '17 at 15:36

answered Nov 28 '17 at 15:30

Juan Carlos Oropeza

47,252
12
78
118

Thank you, I will study throughfully your two tips (max/min issue and Ties). Your code worked partially, I do not understand yet why but in a bigger scenario the product asociated to the max/min value was not correctly recovered. I wonder if it has any relation with the issues you are talking about. @Ebed Kharistian additional `JOIN` statements for the products solved it. – Chang Nov 29 '17 at 08:54

score 0 · Answer 3 · answered Nov 28 '17 at 15:41

Try this, Not sure what you trying to achieve, but as far as I understood, each store has the same product just different cal and price, so you trying to get most cal and the cheapest price.

    Select minprice.Supermarket, MaxCal.Kcal, MaxCal.Product, MinPrice.Price,MinPrice.Product
    From (
    Select supermarket, Min(price) Price, Product from #products
    Group by Supermarket, Product 
    ) MinPrice 
    Left Join (
    Select Supermarket, Max(kcal) as Kcal, Product from #products
    Group by Supermarket, Product ) MaxCal on MinPrice.Supermarket = MaxCal.Supermarket 
and MaxCal.Product= MinPrice.Product

Is it possible to perform a single SQL query which SELECTs two max/min values from different columns?

3 Answers3