I want to get all products that has special price and special price < price.

Can anyone show me how can i achieve that ?

enter image description here

2 Answers 2


The best way I can think of doing this is via an SQL statement

SELECT cpeid1.entity_id, cpeid1.value as price, cpeid2.value as special_price FROM catalog_product_entity_decimal cpeid1 LEFT JOIN catalog_product_entity_decimal cpeid2 ON (cpeid1.entity_id = cpeid2.entity_id AND cpeid2.attribute_id = 76) WHERE cpeid1.attribute_id = 75 AND cpeid2.value IS NOT NULL ;

Where 75 = price attribute id and 76 = special_price attribute id (from the eav_attribute table)


I think you can use this code

        $_productCollection = Mage::getModel('catalog/product')->getCollection();
        array(Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH, Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG))
        ->addAttributeToSort('price_index.price', 'asc')
        ->where('price_index.final_price < price_index.price')
  • This is for Magento 1
    – Gediminas
    Sep 12, 2017 at 12:15
  • yes, that is for M1. do you want that function for m2?
    – Trang Lee
    Sep 13, 2017 at 10:49
  • Not yet, but you may write it for the future :)
    – Gediminas
    Sep 13, 2017 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.