I am trying to fetch 12 products which are latest and sorting them for price. However not able to get the results i needed.
Here is the SQL query.
select p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id, products_description pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' order by products_price asc, p.products_date_added desc limit 12
if i put only order by price then it gives some other products and if i put by date it gives some other products. However i want to get the products which are lastest and among them by sorting with price (min to max) OR (max to min)
SELECT * FROM (
select p.products_id, p.products_image, p.products_tax_class_id, pd.products_name, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id, products_description pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' order by p.products_date_added desc limit 12) AS your_original_query
ORDER BY products_price ASC
but using LIMIT in a subquery doesn’t work in any other database besides mysql and even then, not in every mysql version, although if you’re on version 5 you should be okay