I have yes/no type product attribute.
If this featured attribute is yes for any products, those product will show top(first featured product showing in category page then rest of the products are showing) in the category page including search result as well.
What is the best way to achieve this so it will work perfectly if catalog flat is Enabled.
Please let me know if more information require on this.
Update: I am trying one solution but getting below error:
2 exception(s):
Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'featured' in 'order clause', query was: SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, IFNULL(review_summary.reviews_count, 0) AS `reviews_count`, IFNULL(review_summary.rating_summary, 0) AS `rating_summary`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_category_product_index_store7` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=7 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=458
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
LEFT JOIN `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 7 AND review_summary.entity_type = (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product'))
INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id
INNER JOIN `search_tmp_5e3e93d21adef5_51912155` AS `featured` ON e.entity_id = featured.entity_id ORDER BY `cat_index`.`position` asc, `cat_index`.`position` asc, `featured` DESC, `e`.`entity_id` DESC
LIMIT 12
Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'featured' in 'order clause'