dashCommerce

I have edited CSK_Stats_FavouriteProducts stored procedure from TOP 100 PERCENT to TOP 3 PERCENT and TOP 3 but get the same 3 items under recently viewed regardless of which items have been viewed. How can it be modified to show the last 3 items viewed?

I am not having much luck with this. The list of recently viewed items worked fine when the number of items is set to 100 percent, but this list keeps growing. When I change the number of items it displays the same items. I have tried adding an order by statement by this didn't work.

Can anybody help please?

By seeing the storedprocedure it should return distinct 3 times only.

SELECT DISTINCT TOP 3

Thanks for this suggestion. This has helped as It is displaying three products and they are updating, but they are not updating every time a new product is viewed - I can't make out the criteria that is being applied.

I need to modify the stored procedure further so that it displays the last three.

I have paisted the stored procedure below. I need to update it so that the last three products viewed are shown. Currently is displays three recent products but not the last three.

CREATE PROCEDURE [dbo].[CSK_Stats_FavoriteProducts]
 (
    @userName nvarchar(50)
 )
AS
 
SELECT     DISTINCT TOP 3 CSK_Stats_Tracker.userName, CSK_Store_Product.productGUID, CSK_Store_Product.productName,
                      COUNT(CSK_Stats_Tracker.trackingID) AS ViewCount, CSK_Store_Product.defaultImage, CSK_Store_Product.productID
FROM         CSK_Stats_Tracker INNER JOIN
                      CSK_Store_Product ON CSK_Stats_Tracker.productSKU = CSK_Store_Product.sku
WHERE     (CSK_Stats_Tracker.userName = @userName)
GROUP BY CSK_Stats_Tracker.userName, CSK_Store_Product.productGUID, CSK_Store_Product.productName, CSK_Store_Product.defaultImage,
                      CSK_Store_Product.productID
ORDER BY COUNT(CSK_Stats_Tracker.trackingID)

RETURN

you should change the order by clause

ORDER BY CSK_Stats_Tracker.createdOn DESC

I have edited the stored procedure but it keeps crashing the database server when I try to run it. Any idea what I have done wrong? Sorry if this is a novice question, but should I change "CREATE" to "UPDATE" when I edit the procedure?

CREATE PROCEDURE [dbo].[CSK_Stats_FavoriteProducts]
 (
    @userName nvarchar(50)
 )
AS
 
SELECT     DISTINCT TOP 3 CSK_Stats_Tracker.userName, CSK_Stats_Tracker.createdOn, CSK_Store_Product.productGUID, CSK_Store_Product.productName,
                      COUNT(CSK_Stats_Tracker.trackingID) AS ViewCount, CSK_Store_Product.defaultImage, CSK_Store_Product.productID
FROM         CSK_Stats_Tracker INNER JOIN
                      CSK_Store_Product ON CSK_Stats_Tracker.productSKU = CSK_Store_Product.sku
WHERE     (CSK_Stats_Tracker.userName = @userName)
GROUP BY CSK_Stats_Tracker.userName, CSK_Store_Product.productGUID, CSK_Store_Product.productName, CSK_Store_Product.defaultImage,
                      CSK_Store_Product.productID
ORDER BY CSK_Stats_Tracker.createdOn DESC

ALTER PROCEDURE [dbo].[CSK_Stats_FavoriteProducts]

I am now able to view the last 3 viewed products using the following code:

TOP 3 CSK_Stats_Tracker.userName, CSK_Store_Product.productGUID, CSK_Store_Product.productName, CSK_Store_Product.defaultImage, CSK_Store_Product.productID FROM CSK_Stats_Tracker INNER JOIN CSK_Store_Product ON CSK_Stats_Tracker.productSKU = CSK_Store_Product.sku ORDER BY CSK_Stats_Tracker.CreatedOn DESC RETURN