Mysql SELECT lowest price in TABLE based on unique productid-CodePudding

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"

CodePudding user response：

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

CodePudding user response：

Following this query:

SELECT productid, oldprice, newprice, created_at FROM pricehistory WHERE buyPrice = ( SELECT MIN(oldprice) FROM pricehistory);

CodePudding user response：

SELECT productid, oldprice, created_at FROM pricehistory WHERE oldprice DESC LIMIT 1