How to insert MAX (text) as WHERE clause?-CodePudding

I should be searching based on two WHERE clauses at the same time. One is a simple clause, the other is MAX (text). How to solve?

x = cursor.execute('SELECT product FROM electronics WHERE brand = ? AND MAX(price)' [combo_brand])

Price is a column of a database. I want to take the highest value of the column records. I would like to select the product with the maximum price, and search by it and by brand at the same time.

I am getting this error:

TypeError: string indices must be integers

CodePudding user response：

If you want to select the product having the max price for a given brand, then use:

sql = '''SELECT product
FROM electronics
WHERE brand = ? AND price = (SELECT MAX(e.price)
                             FROM electronics e
                             WHERE e.brand = electronics.brand)'''
x = cursor.execute(sql, (combo_brand,))
records = cursor.fetchall()
print("Total rows are:  ", len(records))
for row in records:
    print(row)

If instead you want the product with the maximum price across all brands, then use:

sql = '''SELECT product
FROM electronics
WHERE brand = ? AND price = (SELECT MAX(price) FROM electronics)'''
x = cursor.execute(sql, (combo_brand,))

But I suspect the first version is what you want.