CODEWITHSUNDEEP
sqlsqlitegroup-by
Chang
Chang

Reputation: 37

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

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!

Upvotes: 1

Views: 144

Answers (3)

Ebed Kharistian
Ebed Kharistian

Reputation: 82

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.

Upvotes: 1

user8513344
user8513344

Reputation:

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

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

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

Upvotes: 0

Related Questions