SELECT DISTINCT TOP 5 in SQL Server
I cannot get this sql query correctly ...
I want to get the last 5 comments from tblComment
. The problem is that I am getting more than one comment with the same ProductID
. I do not want it.
SELECT DISTINCT TOP 5
tblProduct.ProductID,
tblProduct.ProductName,
tblComment.DateAdded
FROM
tblComment
INNER JOIN
tblProduct ON tblProduct.ProductID = tblComment.ProductID
ORDER BY
tblComment.DateAdded DESC
What am I doing wrong?
+3
source to share
3 answers
Assuming your comment table has an id field, try this:
SELECT TOP 5
tblProduct.ProductID,
tblProduct.ProductName,
tblComment.DateAdded
FROM tblComment
JOIN tblProduct ON tblProduct.ProductID = tblComment.ProductID
JOIN (Select ProductID, max(id) as maxid From tblComment Group By ProductId) t on tblComment.id = t.maxid
ORDER BY tblComment.DateAdded DESC
+6
source to share
You will need to select sub - use the following example according to your needs.
SELECT TOP 5 tblProduct.ProductID,
tblProduct.ProductName,
tblComment.DateAdded
FROM tblComment INNER JOIN
tblProduct ON tblProduct.ProductID = tblComment.ProductID
and tblProduct.ProductID
IN (
SELECT tblProduct.ProductID
FROM tblComment
INNER JOIN tblProduct ON tblProduct.ProductID = tblComment.ProductID
GROUP BY tblProduct.ProductID
HAVING count( tblProduct.ProductID ) =1
)
0
source to share
Products are ranked by time of last comment.
This approach uses CTE and rank function. This request is small, but for larger requests, these features can make things more organized and readable.
with lastComment as (
select c.productID, max(DateAdded) DateAdded,
row_number() over(order by max(dateAdded)) rank
from tblComment c
group by c.productID
)
SELECT
tblProduct.ProductID,
tblProduct.ProductName,
tblComment.DateAdded
FROM
tblProduct
join lastComment ON tblProduct.ProductID = lastCommnet.ProductID
WHERE
lastComment.rank >= 5
ORDER BY lastComment.rank
0
source to share