Magento – Magento sort by price: sort by final price instead of price

Short answer: The sorting doesn't work for Yes/No attributes.

Long Answer:
The problem:

When calling addAttributeToSort on a product collection, if the attribute has a source model then Magento calls addValueSortToCollection from that source model to sort the products.
The Yes/No attributes use the source model Mage_Eav_Model_Entity_Attribute_Source_Boolean that does not implement the method addValueSortToCollection For this model the method just returns $this without processing the collection.

A possible solution:
You can rewrite the Mage_Eav_Model_Entity_Attribute_Source_Boolean and add your addValueSortToCollection method. It should basically be the same code as it is in the class Mage_Catalog_Model_Product_Visibility or Mage_Catalog_Model_Product_Status (not entirely sure about this) which is:

public function addValueSortToCollection($collection, $dir = 'asc')
{
    $attributeCode  = $this->getAttribute()->getAttributeCode();
    $attributeId    = $this->getAttribute()->getId();
    $attributeTable = $this->getAttribute()->getBackend()->getTable();

    if ($this->getAttribute()->isScopeGlobal()) {
        $tableName = $attributeCode . '_t';
        $collection->getSelect()
            ->joinLeft(
                array($tableName => $attributeTable),
                "e.entity_id={$tableName}.entity_id"
                    . " AND {$tableName}.attribute_id='{$attributeId}'"
                    . " AND {$tableName}.store_id='0'",
                array());
        $valueExpr = $tableName . '.value';
    }
    else {
        $valueTable1 = $attributeCode . '_t1';
        $valueTable2 = $attributeCode . '_t2';
        $collection->getSelect()
            ->joinLeft(
                array($valueTable1 => $attributeTable),
                "e.entity_id={$valueTable1}.entity_id"
                    . " AND {$valueTable1}.attribute_id='{$attributeId}'"
                    . " AND {$valueTable1}.store_id='0'",
                array())
            ->joinLeft(
                array($valueTable2 => $attributeTable),
                "e.entity_id={$valueTable2}.entity_id"
                    . " AND {$valueTable2}.attribute_id='{$attributeId}'"
                    . " AND {$valueTable2}.store_id='{$collection->getStoreId()}'",
                array()
            );
            $valueExpr = $collection->getConnection()->getCheckSql(
                $valueTable2 . '.value_id > 0',
                $valueTable2 . '.value',
                $valueTable1 . '.value'
            );
    }

    $collection->getSelect()->order($valueExpr . ' ' . $dir);
    return $this;
}

Explanation provided here also for a similar issue

First add a dummy attribute called 'discount_percent' and make it "used for sorting" (just as Giuseppe said in the other answer).
It's not important what type it is or what values it has.
it's easier that way. If you don't want to add the attribute you have to rewrite the method Mage_Catalog_Model_Config::getAttributeUsedForSortByArray to include your attribute. I would rather avoid rewrites as much as possible. Now you need to tell magento to take a special action when the sorting is done by that attribute.
For this you have to rewrite (I don't know other way) the method Mage_Catalog_Model_Resource_Product_Collection::addAttributeToSort()
You will need a new module for that. let's call it Easylife_Sorting.
Here are the files.

app/etc/module/Easylife_Sorting.xml - the declaration file

<?xml version="1.0"?>
<config>
    <modules>
        <Easylife_Sorting>
            <codePool>local</codePool>
            <active>true</active>
            <depends>
                <Mage_Catalog />
            </depends>
        </Easylife_Sorting>
    </modules>
</config>

app/code/local/Easylife/Sorting/etc/config.xml - the configuration file

<?xml version="1.0"?>
<config>
    <modules>
        <Easylife_Sorting>
            <version>1.0.0</version>
        </Easylife_Sorting>
    </modules>
    <global>
        <models>
            <catalog_resource>
                <rewrite>
                    <product_collection>Easylife_Sorting_Model_Resource_Product_Collection</product_collection>
                </rewrite>
            </catalog_resource>
        </models>
    </global>
</config>

app/code/local/Easylife/Sorting/Model/Resource/Product/Collection.php - your class override. Here is where the magic happens.

class Easylife_Sorting_Model_Resource_Product_Collection
    extends Mage_Catalog_Model_Resource_Product_Collection {
    public function addAttributeToSort($attribute, $dir = self::SORT_ORDER_ASC){
        //don't screw up the admin sorting
        if (Mage::app()->getStore()->getId() == Mage_Core_Model_App::ADMIN_STORE_ID){
            return parent::addAttributeToSort($attribute, $dir);
        }
        if ($attribute == 'discount_percent') {
            //determine the discount percent value
            $this->addExpressionAttributeToSelect('discount_percent_value', '(({{price}} - final_price) / {{price}})', array('price'));
            //sort by the discount percent value
            $sortDir = $dir;
            //in case you need to reverse the order so when you sort ascending you will see the most discounts first do this:
            //if not then remove the if-else statement below.
            if ($sortDir == self::SORT_ORDER_ASC) {
                $sortDir = self::SORT_ORDER_DESC;
            }
            else {
                $sortDir = self::SORT_ORDER_ASC;
            }
            $this->getSelect()->order(array('discount_percent_value '.$sortDir));
            return $this;
        }
        //otherwise do nothing special
        return parent::addAttributeToSort($attribute, $dir);
    }
}

This works for sure for simple/virtual/downloadable products. I have no idea what effect has on configurable/bundle and grouped products.