I want to select all the products SKU which have special price through mysql query.

3 Answers 3


For Magento 1.9. Here 76 is attribute_id for special_price attribute.

select cpe.sku,cpe.type_id, cped.store_id, cped.value from catalog_product_entity_decimal cped
RIGHT OUTER JOIN catalog_product_entity cpe on cpe.entity_id = cped.entity_id 
where cped.attribute_id=76
order by cpe.type_id
  • But this won't be the applied final price, need to check the dates also.
    – MagePsycho
    Commented Aug 1, 2017 at 18:29

here is mysql query (please note that table name can contain prefixes)

select e.sku,pd.store_id, pd.value from catalog_product_entity as e 
inner join  catalog_product_entity_decimal as pd on e.entity_id = pd.entity_id 
inner join eav_attribute as a on pd.attribute_id = a.attribute_id 
where a.attribute_code="special_price"

Below code works perfectly for me.

SELECT e.sku,pd.store_id, pd.value FROM catalog_product_entity AS e

INNER JOIN catalog_product_entity_decimal AS pd ON e.entity_id = pd.entity_id

INNER JOIN eav_attribute AS a ON pd.attribute_id = a.attribute_id

WHERE a.attribute_code="special_price"

Your Answer

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

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