SELECT *
FROM
(
SELECT i.*
, [Current sales price] = p.Price
, Manufacturer = m.Name
, [Category] = dbo.[f_TL_Get::Properties_Value]('Items', i.ID, N'კატეგორია')
, [Subcategory] = dbo.[f_TL_Get::Properties_Value]('Items', i.ID, N'ქვეკატეგორია')
, [ID-Category] = dbo.[f_TL_Get::Properties_ValueEx]('Items', i.ID, N'კატეგორია')
, [ID-Subcategory] = dbo.[f_TL_Get::Properties_ValueEx]('Items', i.ID, N'ქვეკატეგორია')
, im.Image, RN = ROW_NUMBER() OVER (ORDER BY im.ID DESC)
FROM (
SELECT *
, rowN = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM
(
SELECT
i.Code
, Name = i.Name
, Description = i.Description
, [ID-Manufacturers]
, [ID-ItemGroups] = i.[ID-ItemGroups]
, ID = i.ID
, [Sales price] = i.[Sales price]
, Quantity = b.Quantity
, [ID-Image] = im.ID
, rowPartitionN = ROW_NUMBER() OVER (PARTITION BY i.Code ORDER BY im.ID DESC)
FROM Items i
RIGHT JOIN
(
SELECT i.Code,
Quantity = SUM(b.Quantity)
FROM Balances AS b WITH (NOLOCK)
LEFT JOIN Items i WITH (NOLOCK)
ON b.[ID-Items] = i.ID
LEFT JOIN Locations l WITH (NOLOCK)
ON b.[ID-Locations] = l.ID
WHERE i.[Active] <> 0 AND l.[ID-LocationGroups] = 1
GROUP BY i.Code
HAVING SUM(b.Quantity) > 2 /* Get items with total quantity greater than 2 */
) b
ON i.Code = b.Code
LEFT JOIN [Images] im WITH (NOLOCK)
ON im.[Table name] = 'Items' AND im.[Record ID] = i.ID
WHERE im.ID IS NOT NULL /* Exclude records without images */
AND i.[ID-ItemGroups] <> 23
AND dbo.[f_TL_Get::Properties_ValueEx]('Items', i.ID, N'კატეგორია') = 603
AND dbo.[f_TL_Get::Properties_ValueEx]('Items', i.ID, N'ქვეკატეგორია') = 664
) i
WHERE i.rowPartitionN = 1
) i
LEFT JOIN Manufacturers m WITH (NOLOCK)
ON i.[ID-Manufacturers] = m.ID
LEFT JOIN [Images] im WITH (NOLOCK)
ON im.ID = i.[ID-Image]
CROSS APPLY dbo.[f_TL_Get::ItemPrice] (i.ID, 1, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) AS p
) i
WHERE i.RN BETWEEN 1 AND 9
