Browse by category
Browse all products
DECLARE @collectionIds1 int = 272;
DECLARE @collectionIds2 int = 258;
DECLARE @collectionIds3 int = 261;
DECLARE @collectionIds4 int = 269;
DECLARE @collectionIds5 int = 262;
DECLARE @collectionIds6 int = 257;
DECLARE @collectionIds7 int = 259;
DECLARE @collectionIds8 int = 299;
DECLARE @collectionIds9 int = 268;
DECLARE @collectionIds10 int = 255;
DECLARE @collectionIds11 int = 267;
DECLARE @collectionIds12 int = 256;
DECLARE @collectionIds13 int = 304;
DECLARE @collectionIds14 int = 260;
DECLARE @collectionIds15 int = 263;
DECLARE @collectionIds16 int = 270;
DECLARE @collectionIds17 int = 265;
DECLARE @collectionIds18 int = 266;
DECLARE @collectionIds19 int = 266;
DECLARE @collectionIds20 int = 266;
DECLARE @siteId int = 4;
DECLARE @GetValueOrDefault int = 10000;
SELECT [s1].[CollectionProductId], [s1].[CollectionId], [s1].[ProductId]
FROM (
SELECT DISTINCT [c].[CollectionId]
FROM [CollectionProduct] AS [c]
INNER JOIN [Collection] AS [c0] ON [c].[CollectionId] = [c0].[CollectionId]
INNER JOIN [Product] AS [p] ON [c].[ProductId] = [p].[ProductId]
WHERE [c].[CollectionId] IN (@collectionIds1, @collectionIds2, @collectionIds3, @collectionIds4, @collectionIds5, @collectionIds6, @collectionIds7, @collectionIds8, @collectionIds9, @collectionIds10, @collectionIds11, @collectionIds12, @collectionIds13, @collectionIds14, @collectionIds15, @collectionIds16, @collectionIds17, @collectionIds18, @collectionIds19, @collectionIds20) AND [c0].[SiteId] = @siteId AND [p].[SiteId] = @siteId AND [p].[IsDeleted] = CAST(0 AS bit)
) AS [s0]
INNER JOIN (
SELECT [s].[CollectionProductId], [s].[CollectionId], [s].[ProductId]
FROM (
SELECT [c1].[CollectionProductId], [c1].[CollectionId], [c1].[ProductId], ROW_NUMBER() OVER(PARTITION BY [c1].[CollectionId] ORDER BY CASE
WHEN [p0].[ProductType] = 'mn' THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END DESC, [p0].[IsInStock] DESC, [p0].[DateTimeUpdated] DESC) AS [row]
FROM [CollectionProduct] AS [c1]
INNER JOIN [Collection] AS [c2] ON [c1].[CollectionId] = [c2].[CollectionId]
INNER JOIN [Product] AS [p0] ON [c1].[ProductId] = [p0].[ProductId]
WHERE [c2].[SiteId] = @siteId AND [p0].[SiteId] = @siteId AND [p0].[IsDeleted] = CAST(0 AS bit)
) AS [s]
WHERE [s].[row] <= @GetValueOrDefault
) AS [s1] ON [s0].[CollectionId] = [s1].[CollectionId]
