SELECT DISTINCT (p.products_id), p.new_arrivals, p.sku,p.vendor_sku,p.is_360_video,p.video_360_cnt,p.product_type,p.product_name,p.center_stone_name,p.center_stone_color,p.diamond_carat_weight,p.clearance,p.style,p.product_gemstone,p.product_url,p.product_center_stone_shape,p.diamond_carat_weight,p.diamond_quality,p.metal_type, prod_group_var as group_var, p.metal_group, case when p.metal_group != '' then CONCAT(p.metal_group,'-',p.diamond_carat_weight,'-',p.diamond_quality,'-',p.product_gemstone,'-',p.product_type,'-',p.product_center_stone_shape,'-',p.width_mm,'-',p.product_center_stone_name,'-',p.zodiac_sign) else p.products_id end as group_var2, p.sale_price,p.our_price,p.retail_price,p.is_build,p.current_stock,p.bc_category_id,pcr.category_id,p.dial_color,p.istryathome,p.tryathomeqty,p.matching_ring_sku,p.matching_band_sku,p.color,p.product_type_group, (CASE p.metal_type WHEN '14K White Gold' THEN 1 WHEN '14K Yellow Gold' THEN 2 WHEN '14K Rose Gold' THEN 3 WHEN '18K White Gold' THEN 4 WHEN '18K Yellow Gold' THEN 5 WHEN '18K Rose Gold' THEN 6 WHEN 'Palladium' THEN 7 WHEN 'Platinum' THEN 8 ELSE 9 END) AS metal_rank, p.group_metals, p.group_gemstones, p.created_metal_gemstone_group, (CASE p.clearance WHEN 'No' THEN p.display_position ELSE 999999 END) AS ship_in_two_days_rank , SUM(p.prod_sold_quantity) as total_sold_quantity , case when (p.sale_price > 2000 or (p.sale_price <= 0 and p.our_price > 2000)) then case when (p.prod_sold_quantity >= 3 and DATE_FORMAT(p.prod_last_order_date,'%Y-%m-%d') >= '2022-03-19') then 1 else 0 end else case when (p.prod_sold_quantity >= 5 and DATE_FORMAT(p.prod_last_order_date,'%Y-%m-%d') >= '2022-03-19') then 1 else 0 end end as best_seller_flag FROM ( SELECT DISTINCT psub.products_id, (CASE psub.metal_type WHEN '14K White Gold' THEN 1 WHEN '14K Yellow Gold' THEN 2 WHEN '14K Rose Gold' THEN 3 WHEN '18K White Gold' THEN 4 WHEN '18K Yellow Gold' THEN 5 WHEN '18K Rose Gold' THEN 6 WHEN 'Palladium' THEN 7 WHEN 'Platinum' THEN 8 ELSE 9 END) AS metal_rank ,(CASE psub.clearance WHEN 'No' THEN psub.display_position ELSE 999999 END) AS ship_in_two_days_rank, IF(psub.current_stock <=0,999999,1) AS stock_rank , IF(psub.prod_sold_quantity > 0,psub.prod_sold_quantity,0) AS prod_sold_quantity FROM `sky_products` AS psub inner join `sky_products_category` AS spcr on (psub.products_id = spcr.products_id) inner join `sky_category` AS psubc on (spcr.category_id = psubc.category_id) WHERE psub.status='1' AND spcr.category_id IN('296') AND psub.clearance_fixed_sku = '0' AND psub.clearance = 'No' ORDER BY (CASE WHEN psub.prod_sold_quantity >0 THEN psub.prod_sold_quantity WHEN psub.vendor_name = 'shy' THEN -1 ELSE 0 END) DESC, metal_rank, psub.display_position ) AS pn, `sky_products` AS p ,`sky_products_category` AS pcr , `sky_category` AS c WHERE p.products_id = pcr.products_id AND p.status='1' AND p.vendor_sku NOT IN('SAMPLE-1','SAMPLE-2','SAMPLE-3','SAMPLE-4','SAMPLE-5','SAMPLE-6','SAMPLE-7','SAMPLE-8','SAMPLE-9','SAMPLE-10') AND pcr.category_id = c.category_id AND c.status = '1' AND p.designer != 'invicta' AND (CASE WHEN p.clearance ='Yes' THEN 1 ELSE p.current_stock>0 END) AND p.products_id = pn.products_id AND pcr.category_id IN('296') AND p.clearance_fixed_sku = '0' AND p.clearance = 'No' GROUP BY (CASE group_var WHEN '' THEN p.products_id ELSE group_var END) ORDER BY (CASE WHEN SUM(pn.prod_sold_quantity) >0 THEN SUM(pn.prod_sold_quantity) WHEN p.vendor_name = 'shy' THEN -1 ELSE 0 END) DESC, metal_rank, p.display_position LIMIT 0 , 24
Dome Wedding Rings Allurez carries a wide variety of plain dome comfort fit wedding rings for women. Choose from a selection of platinum wedding rings, rose gold wedding rings, white gold wedding rings, palladium wedding rings and yellow gold wedding rings, each of which will make her feel very special. You are sure to find the right type you are looking for. We carry 14k wedding rings, 18k wedding rings, 950 palladium wedding rings and 950 platinum wedding rings. We offer a vast collection woman’s wedding rings in different karat options, widths and finishes. Alllurez Jewelers has widths of 2mm wedding rings, 3mm wedding rings, 4mm wedding rings, 5mm wedding rings, 6mm wedding rings, 7mm wedding rings, as well as other widths. You can also select the best type of finish for your dome wedding ring for her, as we have a variety to choose from, including: sand blast finishes, cross stained finishes, matt finishes, stain/brushed finishes and hammered finishes. So go ahead browse our selection to buy a women’s wedding ring.