How can I fetch products with the highest price in SQL with LIMIT-CodePudding

I tried to fetch 50 products from a database with the highest price but if i set the LIMIT at 50 it's just fetching the first 50 products order by the price. This is not what i want. How can i setup the mysql query right or should i fetch all and set the limit in the php fetch_assoc()?

SQL Query:

SELECT id, product_name, product_url, product_price, product_delivery_time, product_on_stock, product_language, product_type 
FROM product 
WHERE is_active = '1' AND not product_price = 'N/A' 
  AND product_price > (SELECT max(product_price) from product)

I tried different SQL queries but without success. I'm not familiar with sub-queries and i think somewhere their is the problem.

CodePudding user response：

Order by price. Without any knowledge of the schema this could help.

order by price desc limit 50

CodePudding user response：

The problem was the DataType:

product_price = db.Column(db.String(80))

I changed it in:

product_price = db.Column(db.Numeric(15, 2))

and now the (modified) sql query is working:

SELECT id, product_name, product_url, product_price, product_delivery_time, product_on_stock, product_language, product_type 
FROM product 
WHERE is_active = '1' AND NOT product_price = '0' 
ORDER BY product_price DESC LIMIT 50

Thanks everyone for the help.