We help IT Professionals succeed at work.

Sql query

Gurbirs
Gurbirs asked
on
Hi,
I have two tables PRODUCTS and WEBSITEVISITS. I want to get top 3 distinct recently viewed products. I have PRODUCT.ID and WEBSITEVISITS.REFERENCEID common field. I am sending the script to two tables as attachment. Please have a look.

Thanks in advance

  SqlQuery.txt
Comment
Watch Question

Top Expert 2011

Commented:
select top 3 prodcut.name, some other fields

from product p inner join websitevists w on p.id = w.referenceid

order by w.id desc

That's how I would approach it
Scott PletcherSenior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
SELECT DISTINCT TOP 3 ReferenceID
FROM [WebsiteVisits]
ORDER BY ID DESC

Author

Commented:
I am already using following query

select top 3  p.* from WebsiteVisits wv join Products p on wv.ReferenceID=p.ID  where wv.ReferenceType='Product Visit' order by wv.CreatedDate desc

but this query does not bring the distinct products. My requirement is to bring the top 3 products recently viewed. When a customer visit the particular product I put a entry in websitevisits table.  Please see the data of two tables that I have attached previously.

Waiting for your quick reply.
Scott PletcherSenior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
SELECT p.*, wv.*
FROM (
    SELECT DISTINCT TOP 3 ReferenceID
    FROM dbo.[WebsiteVisits]
    ORDER BY ID DESC
) AS wv
INNER JOIN dbo.Products p ON
    p.ID = wv.ReferenceID

Author

Commented:
SELECT p.*, wv.*
FROM (
    SELECT DISTINCT TOP 3 ReferenceID
    FROM dbo.[WebsiteVisits]
    ORDER BY CreatedDate DESC
) AS wv
INNER JOIN dbo.Products p ON
    p.ID = wv.ReferenceID

Above suggested query return error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified."
Scott PletcherSenior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Rats, sorry, please try this:


SELECT p.*, wv.*
FROM (
    SELECT TOP 3 ReferenceID, ROW_NUMBER() OVER (PARTITION BY ReferenceID ORDER BY ID DESC) AS row_num
    FROM dbo.[WebsiteVisits]
) AS wv
INNER JOIN dbo.Products p ON
    p.ID = wv.ReferenceID

Open in new window

Author

Commented:
Above given solution is not working for me.  I think there is some communication gap. I am trying to explain my problem again.  I have two tables 1) Products and 2) WebsiteVisits (For more detail you can see my attached database script that contain the table structure and table data that I provided while posting my question first time )

I put entry into websitevisit table each time someone visit the product detail page of my website with datetime. Product tables primary key act as foreign key in websitevisit table (For more detail you can see my attached database script that contain the table structure and table data ) . This means my websitevisits table can contain multiple entries of same product with different time. Now I have to select top 3 distinct recently viewed products order by datetime descending from websitevisite joined with products table so that I get product details.
My select query needs to full fill the following conditions.

1.  Top 3 product details should be unique. Means if websitevisit table contail last 3 entries of same product then my select query should select 1 product details out of three and select other 2 records from websitevisits and product table. Resulted record after joining two table should be distinct means resulted record should not contain same product detail.(Above suggested solution does not fulfill this condition)


















Senior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:

SELECT wv.*, p.*
FROM (
    SELECT TOP 3 ReferenceID, MaxID
    FROM (
        SELECT ReferenceID, MAX(ID) AS MaxID
        FROM (
            SELECT TOP 500 ID, ReferenceID --or 1000, if you need that many to insure 3 unique #s
            FROM dbo.[WebsiteVisits]
            ORDER BY ID DESC
        ) AS derived
        GROUP BY ReferenceID
    ) AS derived2
    ORDER BY MaxID DESC
) AS wvMax
INNER JOIN dbo.[WebsiteVisits] wv ON
    wv.ID = wvMax.MaxID
LEFT OUTER JOIN dbo.Products p ON
    p.ID = wvMax.ReferenceID

Open in new window

Author

Commented:
Happy to receive quick response.
Thanks
Scott PletcherSenior DBA
SILVER EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Sorry for the previous issues.  Was trying to find an efficient way to do it, w/o being forced to scan the full WebsiteVisits table, which I thought could be a (very) large table.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.