SELECT bt_item.`id` AS `id`, bt_item.`nm` AS `nm`, bt_item.`deleted_ind` AS `deleted_ind`, bt_item.`display_ind` AS `display_ind`, bt_item.`descr` AS `descr`, if((bt_item.`photo_ind` = _utf8"Y"),1,0) AS `photo`, bt_item.`type_cd` AS `type_cd`, bt_item.`gift_ind` AS `gift_ind`, bt_product.`sku_code` AS `sku_code`, bt_product.`status_cd` AS `product_status`, bt_product.`buy_from_partner` AS `buy_from_partner`, bt_product.`partner_link` AS `partner_link`, bt_product.`price` AS `price`, bt_product.`sale_price` AS `SalePrice`, bt_wine.`vintage` AS `vintage`, bt_wine.`country_id` AS `cid`, bt_wine.`region_id` AS `rid`, bt_wine.`sub_region_id` AS `srid`, bt_wine.`appellation_id` AS `aid`, (select avg(`bt_item_rating`.`score`) AS `AVG(score)` from `bt_item_rating` where (`bt_item_rating`.`item_id` = `bt_item`.`id`) group by `bt_item_rating`.`item_id`) AS `exact_rate`, (select count(0) AS `COUNT(*)` from `bt_item_note` where (`bt_item_note`.`item_id` = `bt_item`.`id`)) AS `note_count`, (select count(0) AS `COUNT(*)` from `bt_item_rating` where (`bt_item_rating`.`item_id` = `bt_item`.`id`)) AS `rating_count`, (select count(0) AS `COUNT(*)` from `bt_order_line` where (`bt_order_line`.`product_id` =`bt_item`.`id`)) AS `sellers_count`, (select count(0) AS `COUNT(*)` from `bt_item_tag` where (`bt_item_tag`.`item_id` = `bt_item`.`id`)) AS `tag_count` FROM ((bt_item left join bt_product on((bt_item.id = bt_product.id))) left join bt_wine on ((bt_item.id =bt_wine.id))) left join bt_country c on bt_wine.country_id=c.id left join bt_region r on bt_wine.region_id=r.id left join bt_sub_region sr on bt_wine.sub_region_id=sr.id left join bt_winery w on bt_wine.winery_id=w.id WHERE TRUE AND type_cd = "WINE" and bt_item.deleted_ind = "N" and bt_item.display_ind="Y" and bt_item.id in() ORDER BY if(status_cd = "AVAILABLE", 0, 1) ASC, if(status_cd = "OUTOFSTOCK", 0, 1) ASC, price ASC,sale_price ASC Limit 0,10