[FIXED] Mysql SELECT lowest price in TABLE based on unique productid

Issue

I regularly scrape product prices and store them in the pricehistory table.

Example table:

 id     productid   oldprice    newprice    created_at
20      B0B136KCXP  1571.54 1687.31 2022-09-27 14:38:34
527     B0B136KCXP  1687.31 1598.39 2022-09-27 21:28:04
1317    B0B136KCXP  1598.39 1798.19 2022-09-28 13:51:02
1344    B0B136KCXP  1798.19 1897.10 2022-09-28 14:16:59
1859    B0B136KCXP  1897.10 1587.41 2022-09-28 22:23:39
5894    B0B136KCXP  1587.41 1883.12 2022-09-30 22:22:47
6433    B0B136KCXP  1883.12 1873.13 2022-10-01 09:56:28
9080    B0B136KCXP  1873.13 1883.12 2022-10-03 16:16:43
9256    B0B136KCXP  1883.12 1873.13 2022-10-03 19:54:40
10112   B0B136KCXP  1873.13 1860.13 2022-10-04 07:47:43
10290   B0B136KCXP  1860.13 1798.20 2022-10-04 11:09:52

In this table I want to select the lowest price by productid. I will send this data I have taken as a notification via telegram bot.

I tried a few times but was unsuccessful. I want to perform this operation in scrapy pipelines.py file.

What I want to choose:

productid, lowest.oldprice, created_at

Thanks in advance for your help.

My notification function:

def sendnotifications(self, token):
        cursor = self.cnx.cursor()
        req = requests
        cursor.execute("SELECT * FROM notificate WHERE token= '"+token+"'")
        notifications = cursor.fetchall()
        for notification in notifications:
            print(notification)
            productid = notification[1]
            url = notification[3]
            name = notification[2]
            old = notification[4]
            new = notification[5]
            price_difference = old - new
            percentage = price_difference / old
            percentage_str = str("%.2f" % (percentage * 100))

        cursor.execute("SELECT p1.productid,p1.oldprice,p1.created_at FROM pricehistory p1 JOIN (SELECT min(oldprice) as minprice,productid FROM pricehistory GROUP BY productid) p2 ON p1.productid=p2.productid and p1.oldprice=p2.minprice")
        pricehistory = cursor.fetchall()
        for history in pricehistory:
            productid = history[0]
            oldprice = history[1]
            created_at = history[2]
            

            message = "<b>" + name + "</b>" + "\n\n" + \
                str(old) + " TL >>>> " + \
                str(new) + f" TL - <b>{percentage_str}%</b>" + "\n\n" + \
                url + "\n\n" + \
                "Min Price History:" + oldprice + created_at + "\n\n"
                
            if str(old) == "1.00" or str(old) == "2.00":
                message = "<b>" + name + "</b>" + "\n\n" + \
                    "<b>" + str(new) + " TL\n\n" + "</b>" + \
                    url + "\n\n" + \
                    "Min Price History:" + oldprice + created_at + "\n\n"

Solution

If you just want to get the lowest price,then you can using following sql

SELECT min(oldprice),productid FROM pricehistory
GROUP BY productid 

If you want to get created_at together,then can using following sql

SELECT p1.productid,p1.oldprice,p1.created_at FROM pricehistory p1
 JOIN
 (
  SELECT min(oldprice) as minprice,productid FROM pricehistory
  GROUP BY productid 
) p2 ON p1.productid=p2.product_id and p1.oldprice=p2.minprice

Answered By - lucumt