The other day I had to create a new filter option for products which could order them by the largest discount. I made this simple workaround where I joined product collection with table “catalog_product_entity_decimal”, which is the table containing all the prices. “attribute_id” with value 76 is special_price.
Then, I created a new column “discount” which is calculated as:
discount = (price – special_price) / (price / 100)
// Join the special price into the discount column. $store = Mage::app()->getStore(); $_productCollection->getSelect() ->joinLeft( array( 'sfoi' => 'catalog_product_entity_decimal' ), 'e.entity_id = sfoi.entity_id AND sfoi.attribute_id = 76 AND sfoi.store_id = ' . $store->getId(), array( 'discount' => '((price - sfoi.value) / (price / 100))' ) ); // Set order to DESC $_productCollection->getSelect()->Order( array( new Zend_Db_Expr( 'discount DESC' ) ) );