SELECT CompanyID, CompanyName, Add1, Add2, Add3, CityNameCo , CountryID, Pincode, TelStdCode, TelNo, FaxStdCode, FaxNo, email, website, Activities, isElite, eliteuserfilepath, EliteEnteredDate, EliteValidTillDate, producttitle, ProductDesc, productimagefilepath, eliteConfirmed, CompanyProdID , CountryNameCo , InternetTypeCode, brandimage, brandname, countryshortcodeco , score, '' as CategoryList , dontshowincountries, Google_store_applink, Apple_store_applink,price,pricecurrency,productrating,whatsappnoforchat
FROM (
SELECT CompanyID, CompanyName, Add1, Add2, Add3, CityNameCo , CountryID, Pincode, TelStdCode, TelNo, FaxStdCode, FaxNo, email, website, Activities, isElite, eliteuserfilepath, EliteEnteredDate, EliteValidTillDate, producttitle, ProductDesc, productimagefilepath, eliteConfirmed, CompanyProdID , CountryNameCo , InternetTypeCode, brandimage, brandname, countryshortcodeco , score, '' as CategoryList , dontshowincountries, Google_store_applink, Apple_store_applink,price,pricecurrency,productrating,whatsappnoforchat ,
ROW_NUMBER() OVER (PARTITION BY CompanyID ORDER BY score DESC, eliteConfirmed DESC, InternetTypeCode ASC) AS rn
FROM (
(
select a.CompanyID,CompanyName,Add1,Add2,Add3,CityNameCo,a.CountryID,Pincode,TelStdCode,TelNo,FaxStdCode,FaxNo,email,website,Activities,isElite, eliteuserfilepath,EliteEnteredDate,EliteValidTillDate,producttitle,ProductDesc,productimagefilepath,eliteConfirmed ,CompanyProdID, CountryNameCo,InternetTypeCode,'' as brandimage,'' as brandname,countryshortcodeco,dontshowincountries,Google_store_applink,Apple_store_applink,price,pricecurrency, productrating,whatsappnoforchat , 65 as score from mstcompany_forsearch a inner join trncompanyprod_forsearch b on a.companyid=b.companyid where match (producttitle) against ('hot rolled steel coil') > 6 and eliteConfirmed = 1 and ( GCCElite='1' or AllUAEElite='1') and b.Productid='5487' limit 0, 250
)
UNION ALL
(
select a.CompanyID,CompanyName,Add1,Add2,Add3,CityNameCo,a.CountryID,Pincode,TelStdCode,TelNo,FaxStdCode,FaxNo,email,website,Activities,isElite, eliteuserfilepath,EliteEnteredDate,EliteValidTillDate,producttitle,ProductDesc,productimagefilepath,eliteConfirmed ,CompanyProdID, CountryNameCo,InternetTypeCode,'' as brandimage,'' as brandname,countryshortcodeco,dontshowincountries,Google_store_applink,Apple_store_applink,price,pricecurrency, productrating,whatsappnoforchat , 50 as score from mstcompany_forsearch a inner join trncompanyprod_forsearch b on a.companyid=b.companyid where eliteConfirmed = 1 and ( GCCElite='1' or AllUAEElite='1') and b.Productid='5487' limit 0, 250
)
UNION ALL
(
select a.CompanyID,CompanyName,Add1,Add2,Add3,CityNameCo,a.CountryID,Pincode,TelStdCode,TelNo,FaxStdCode,FaxNo,email,website,Activities,isElite, eliteuserfilepath,EliteEnteredDate,EliteValidTillDate,producttitle,ProductDesc,productimagefilepath,eliteConfirmed ,CompanyProdID, CountryNameCo,InternetTypeCode,'' as brandimage,'' as brandname,countryshortcodeco,dontshowincountries,Google_store_applink,Apple_store_applink,price,pricecurrency, productrating,whatsappnoforchat , 40 as score from mstcompany_forsearch a inner join trncompanyprod_forsearch b on a.companyid=b.companyid where match (producttitle) against ('"hot rolled steel coil"') > 10 and eliteConfirmed = 1 and ( GCCElite='1' or AllUAEElite='1') limit 0, 250
)
UNION ALL
(
select a.CompanyID,CompanyName,Add1,Add2,Add3,CityNameCo,a.CountryID,Pincode,TelStdCode,TelNo,FaxStdCode,FaxNo,email,website,Activities,isElite, eliteuserfilepath,EliteEnteredDate,EliteValidTillDate,producttitle,ProductDesc,productimagefilepath,eliteConfirmed ,CompanyProdID, CountryNameCo,InternetTypeCode,'' as brandimage,'' as brandname,countryshortcodeco,dontshowincountries,Google_store_applink,Apple_store_applink,price,pricecurrency, productrating,whatsappnoforchat , 15 as score from mstcompany_forsearch a inner join trncompanyprod_forsearch b on a.companyid=b.companyid where a.cityid ='1' and b.Productid='5487' and isElite = 0 AND EliteValidTillDate < '2025-06-27' limit 0, 250
)
UNION ALL
(
select a.CompanyID,CompanyName,Add1,Add2,Add3,CityNameCo,a.CountryID,Pincode,TelStdCode,TelNo,FaxStdCode,FaxNo,email,website,Activities,isElite, eliteuserfilepath,EliteEnteredDate,EliteValidTillDate,producttitle,ProductDesc,productimagefilepath,eliteConfirmed ,CompanyProdID, CountryNameCo,InternetTypeCode,'' as brandimage,'' as brandname,countryshortcodeco,dontshowincountries,Google_store_applink,Apple_store_applink,price,pricecurrency, productrating,whatsappnoforchat , match (producttitle) against ('hot rolled steel coil') + 2 as score from mstcompany_forsearch a inner join trncompanyprod_forsearch b on a.companyid=b.companyid where match (producttitle) against ('hot rolled steel coil') > 6 and isElite=0 and a.cityid ='1' and b.Productid='5487' limit 0, 250
)
UNION ALL
(
select a.CompanyID,CompanyName,Add1,Add2,Add3,CityNameCo,a.CountryID,Pincode,TelStdCode,TelNo,FaxStdCode,FaxNo,email,website,Activities,isElite, eliteuserfilepath,EliteEnteredDate,EliteValidTillDate,producttitle,ProductDesc,productimagefilepath,eliteConfirmed ,CompanyProdID, CountryNameCo,InternetTypeCode,'' as brandimage,'' as brandname,countryshortcodeco,dontshowincountries,Google_store_applink,Apple_store_applink,price,pricecurrency, productrating,whatsappnoforchat , match (producttitle) against ('"hot rolled steel coil"') as score from mstcompany_forsearch a inner join trncompanyprod_forsearch b on a.companyid=b.companyid where match (producttitle) against ('"hot rolled steel coil"') > 6 and isElite = 0 and a.cityid ='1' limit 0, 250
)
UNION ALL
(
select a.CompanyID,CompanyName,Add1,Add2,Add3,CityNameCo,a.CountryID,Pincode,TelStdCode,TelNo,FaxStdCode,FaxNo,email,website,Activities,isElite, eliteuserfilepath,EliteEnteredDate,EliteValidTillDate,producttitle,ProductDesc,productimagefilepath,eliteConfirmed ,CompanyProdID, CountryNameCo,InternetTypeCode,'' as brandimage,'' as brandname,countryshortcodeco,dontshowincountries,Google_store_applink,Apple_store_applink,price,pricecurrency, productrating,whatsappnoforchat , match (producttitle) against ('hot rolled steel coil') as score from mstcompany_forsearch a inner join trncompanyprod_forsearch b on a.companyid=b.companyid where match (producttitle) against ('hot rolled steel coil') > 6 and isElite = 0 and a.cityid ='1' limit 0, 250
)
UNION ALL
(
select a.CompanyID,CompanyName,Add1,Add2,Add3,CityNameCo,a.CountryID,Pincode,TelStdCode,TelNo,FaxStdCode,FaxNo,email,website,Activities,isElite, eliteuserfilepath,EliteEnteredDate,EliteValidTillDate,producttitle,ProductDesc,productimagefilepath,eliteConfirmed ,CompanyProdID, CountryNameCo,InternetTypeCode,'' as brandimage,'' as brandname,countryshortcodeco,dontshowincountries,Google_store_applink,Apple_store_applink,price,pricecurrency, productrating,whatsappnoforchat , 7 as score from mstcompany_forsearch a inner join trncompanyprod_forsearch b on a.companyid=b.companyid where a.cityid ='1' and match(activities) AGAINST ('"hot rolled steel coil"') > 10 limit 0, 250
)
UNION ALL
(
select a.CompanyID,CompanyName,Add1,Add2,Add3,CityNameCo,a.CountryID,Pincode,TelStdCode,TelNo,FaxStdCode,FaxNo,email,website,Activities,isElite, eliteuserfilepath,EliteEnteredDate,EliteValidTillDate,producttitle,ProductDesc,productimagefilepath,eliteConfirmed ,CompanyProdID, CountryNameCo,InternetTypeCode,'' as brandimage,'' as brandname,countryshortcodeco,dontshowincountries,Google_store_applink,Apple_store_applink,price,pricecurrency, productrating,whatsappnoforchat , 6 as score from mstcompany_forsearch a inner join trncompanyprod_forsearch b on a.companyid=b.companyid where match(activities) AGAINST ('hot rolled steel coil') > 15 limit 0, 250
)
) AS unioned
) AS ranked
WHERE rn = 1
ORDER BY score desc ,EliteConfirmed desc , InternetTypeCode asc , productimagefilepath desc Hot Rolled Steel Coil Suppliers in Dubai, Top Suppliers of Hot Rolled Steel Coil in Dubai , UAE
DUBAI YELLOW PAGESONLINE 
