First of all get rid of all those unions - replace them with an
IN clause[
^]
Next - the DISTINCT needs to come after the TOP 1
Finally
ORDER BY DESC[
^] goes at the end.
If you were still using UNION the order by would still go right at the end. Your query becomes
select top 1 distinct a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image
from tbl_products_web as a
inner join tbl_category_web as b on a.category_id=b.category_id
where b.category_id IN (1,2,3,4,5,6)
ORDER BY [whichever column here]
[EDIT - solution corrected based on OP comment]
There are a few ways you can achieve this
Option 1 - using Common Table Expressions
-- NOTE ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
-- so the select from C1, C2 etc redefines the list of columns
with C1 as (select distinct top 1 a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated
from tbl_products_web as a inner join tbl_category_web as b on a.category_id=b.category_id where b.category_id=1 order by a.image_updated desc),
C2 as (select distinct top 1 a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated
from tbl_products_web as a inner join tbl_category_web as b on a.category_id=b.category_id where b.category_id=2 order by a.image_updated desc),
C3 as (select distinct top 1 a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated
from tbl_products_web as a inner join tbl_category_web as b on a.category_id=b.category_id where b.category_id=3 order by a.image_updated desc),
C4 as (select distinct top 1 a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated
from tbl_products_web as a inner join tbl_category_web as b on a.category_id=b.category_id where b.category_id=4 order by a.image_updated desc),
C5 as (select distinct top 1 a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated
from tbl_products_web as a inner join tbl_category_web as b on a.category_id=b.category_id where b.category_id=5 order by a.image_updated desc),
C6 as (select distinct top 1 a.product_price, a.product_id, a.product_name, b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated
from tbl_products_web as a inner join tbl_category_web as b on a.category_id=b.category_id where b.category_id=6 order by a.image_updated desc)
select product_price, product_id, product_name,category_id, category_name,small_image,product_image from C1
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C2
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C3
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C4
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C5
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C6
Option 2 - using a temporary table and a loop
create table #temp
(
product_price float,
product_id int,
product_name varchar(255),
category_id int,
category_name varchar(255),
small_image varchar(max),
product_image varchar(max),
image_updated datetime
)
declare @i int = 1
while @i <= 6
begin
insert into #temp select distinct top 1 a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated
from tbl_products_web as a inner join tbl_category_web as b on a.category_id=b.category_id where b.category_id=@i order by a.image_updated desc
set @i = @i + 1
end
select product_price, product_id, product_name,category_id, category_name,small_image,product_image
from #temp