Selecting products which are latest and with price sort

Databases
1

Hi,

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)

How can i achieve this please ?

Thanks

2

use this –


ORDER BY p.products_date_added DESC LIMIT 12

retrieve the 12 rows, and sort them in an array in your application language (php or whatever you’re using)

3

so there is no way to have both with one sql query ?

4

you can try this …


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