This is not a full answer but it can get you on the right track.
There is how you can get all the products that have a discount
$collection = Mage::getModel('catalog/product')->getCollection();
$collection
->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
->addMinimalPrice()
->addFinalPrice()
->addTaxPercents()
->addUrlRewrite();
Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);
$collection->getSelect()->where("`price_index`.price > price_index.min_price");
$collection->addExpressionAttributeToSelect('percentage', '(price_index.price - price_index.min_price) * 100 / price_index.price', array());
$collection->getSelect()->order('percentage DESC');
I'm not sure this works for other than simple products, and you might get in trouble if you have prices that are 0.00
. So you might want to add to the code above:
$collection->addAttributeToFilter('type_id', 'simple'); // only simple products
$collection->getSelect()->where('price_index.price > ?', 0); //only price bigger than 0;
Now you have 2 options.
Either create a page with a block that renders the collection above. This way you have access real time to the discounted products. The downside is that you don't have the features offered by a category display and you have to implement them yourself (paging, layered navigation, ....).
The second option (I would go with this one) is to have a cron that runs every night (or whenever you want but after the price index is rebuilt) that gets the product collection as described above and assigns all the products in a category and adding their position in the order determined by the percentage discount.
Your cron can look like this:
$collection = ...//see the code above.
$categoryId = 100; //replace with your category id
$category = Mage::getModel('catalog/category')->load($categoryId);
$products = array();
$position = 1;
foreach ($collection as $item) {
$products[$item->getId()] = $position;
$position++;
}
$category->setPostedProducts($products);
$category->save();
Good luck.