Posted by Erland Sommarskog on 10/12/07 21:21

blackpuppy (mingzhu.z@gmail.com) writes:
> I would like to use the following SQL statement to get the latest
> comment for all products.
>
> SELECT PRODUCT_ID, COMMENT, UPDATED_ON
> FROM PRODUCT_COMMENT
> GROUP BY PRODUCT_ID
> HAVING UPDATED_ON = MAX(UPDATED_ON)
>
> But this leads to the following error:
> Column 'PRODUCT_COMMENT.UPDATED_ON' is invalid in the HAVING clause
> because it is not contained in either an aggregate function or the
> GROUP BY clause.
>
> Is there a way to do that?

Here is an alternative to Roy's query that may run faster:

WITH numbered_comments AS (
SELECT PRODUCT_ID, COMMENT, UPDATED_ON,
rowno = row_number() OVER(PARTITION BY PRODUCT_ID
ORDER BY UPDATE_ON DESC)
FROM PRODUCT_COMMENT
)
SELECT PRODUCT_ID, COMMENT, UPDATED_ON
FROM numbered_comments
WHERE rowno = 1

This query only runs on SQL 2005.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Indexed View Crashes ASP.NET App
• Prev in forum: Re: Would special character cause insertion problem?
• Thread view: Re: get value of a single record instead of aggregated value with GROUP BY

[Reply to this message]