SQL Server查询：获取用户最近浏览的唯一产品变体最新20条记录

Got it, let's tackle this duplicate product variant issue in your recent views query. The problem with using DISTINCT here is that even if two rows share the same productVariantId, other columns like dateCreated or productRecentlyViewedId are unique—so DISTINCT can't collapse those into a single row for the variant. Instead, we need to first grab the most recent entry for each unique productVariantId, then join that with your other tables to get the final result.

Here's the revised query using a CTE (Common Table Expression) and window function to filter for the latest entry per variant:

WITH RecentUniqueVariants AS (
    SELECT 
        rv.productId,
        rv.productRecentlyViewedId,
        rv.dateCreated,
        rv.lang,
        rv.isUserLoggedIn,
        rv.userId,
        rv.productVariantId,
        rv.cookieId,
        -- Assign a row number to each variant's entries, ordered by newest first
        ROW_NUMBER() OVER (PARTITION BY rv.productVariantId ORDER BY rv.dateCreated DESC) AS rn
    FROM ProductRecentlyViewed rv
    WHERE rv.lang = 'NO' 
      AND rv.cookieId = CONVERT(uniqueidentifier, '1f102c74-278b-430e-8129-1261dfc7e2ac')
)
SELECT TOP 20
    ruv.productId,
    p.productNameNO AS productName,
    c.picid,
    c.picurl,
    ruv.productRecentlyViewedId,
    ruv.dateCreated,
    ruv.lang,
    ruv.isUserLoggedIn,
    ruv.userId,
    ruv.productVariantId
FROM RecentUniqueVariants ruv
INNER JOIN Product AS p ON ruv.productId = p.productId
LEFT JOIN (
    SELECT productid, picurl, picid, 
           ROW_NUMBER() OVER (PARTITION BY productid ORDER BY isfrontpic DESC) rn 
    FROM productpic
) c ON c.rn = 1 AND ruv.productId = c.productId
WHERE ruv.rn = 1 -- Only keep the latest entry for each variant
ORDER BY ruv.dateCreated DESC; -- Sort by newest first to get the latest 20

How this works: #

• CTE RecentUniqueVariants: This step processes the raw ProductRecentlyViewed data. We use ROW_NUMBER() partitioned by productVariantId to group all entries for the same variant, then order those groups by dateCreated DESC so the newest entry gets a row number of 1.
• Filter for unique variants: In the main query, we only select rows where rn = 1—this ensures each productVariantId appears exactly once, using its most recent entry.
• Join with other tables: We keep your existing joins to Product (for product names) and productpic (for the front-facing product image) to retain all the details you need.
• Sort and limit: Finally, we sort by dateCreated DESC and take the top 20 to get the latest unique viewed variants.

This approach avoids duplicates because we're explicitly selecting only the most recent record per product variant, rather than trying to deduplicate after the fact with DISTINCT.

内容的提问来源于stack exchange，提问作者Nick Developer