SELECT products.ID, products.ProductTitle, Products.CardLayout, Products.IsOwnDesign, Products.ProductSize, Products.ProductSet, Products.Page_Number, Products.SyncID, IsNull([products_featured_dates].DisplayOrder, null) FeaturedDisplayOrder, IsNull([products_membership_links].DisplayOrder, null) MembershipDisplayOrder, IsNull([products_user_links].DisplayOrder, null) UserDisplayOrder FROM [products] LEFT OUTER JOIN [products_user_links] on ([products_user_links].ProductID = [products].ID) LEFT OUTER JOIN [products_membership_links] ON ([products_membership_links].ProductID = products.ID and products_membership_links.MembershipType = 100024) LEFT OUTER JOIN [products_featured_dates] ON([products_featured_dates].ProductID = products.ID) where products.id IN ( SELECT (select top 1 id from products where products.ProductSet = p.ProductSet order by products.productsetdisplayorder) as [ID] FROM product_categories_links pcl INNER JOIN product_categories ON(product_categories.ID = pcl.PRODUCT_CATEGORIES_ID) INNER JOIN products P ON(pcl.PRODUCT_ID = P.ID) INNER JOIN products_membership_links pml ON(P.ID = pml.ProductID) INNER JOIN productsize ON(P.ProductSize = productsize.ID) WHERE P.FLAG_ACTIVE = 1 AND P.Page_Number = 1 and P.IsOwnDesign = 0 AND pml.MembershipType = 100024 AND pcl.PRODUCT_CATEGORIES_ID in (2000) GROUP BY P.ProductSet) ORDER BY UserDisplayOrder, case when GetDate() between CONVERT(DateTime, DATEADD(year, DatePart(year, getdate()) - DatePart(year, [products_featured_dates].StartDate), [products_featured_dates].StartDate)) AND CONVERT(DateTime, DATEADD(year, DatePart(year, getdate()) - DatePart(year, [products_featured_dates].EndDate), [products_featured_dates].EndDate)) then IsNull([products_featured_dates].DisplayOrder, 0) else IsNull([products_membership_links].DisplayOrder, 0) end;