Question 112966 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How can I fetch products with the highest price in SQL with LIMIT

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.

Tags:

2

Answers


  1. 0

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

    order by price desc limit 50

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search