Sql query

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

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

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.

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

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."

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

Select allOpen in new window

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)

Happy to receive quick response.
Thanks

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.