How to get a record value instead of aggregated value with GROUP BY?

Assume that I have a PRODUCT_COMMENT table defined as below. It logs
the multiple comments for products. A product may have multiple
comments logged at different time.

CREATE TABLE [dbo].[PRODUCT_COMMENT](
[COMMENT_ID] [int] IDENTITY(1,1) NOT NULL,
[PRODUCT_ID] [int] NOT NULL,
[COMMENT] [nvarchar](50) NULL,
[UPDATED_ON] [datetime] NOT NULL,
CONSTRAINT [PK_PRODUCT_COMMENT] PRIMARY KEY CLUSTERED
(
[COMMENT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[PRODUCT_COMMENT] WITH CHECK ADD CONSTRAINT
[FK_PRODUCT_COMMENT_PRODUCT] FOREIGN KEY([PRODUCT_ID])
REFERENCES [dbo].[PRODUCT] ([PRODUCT_ID])
GO
ALTER TABLE [dbo].[PRODUCT_COMMENT] CHECK CONSTRAINT
[FK_PRODUCT_COMMENT_PRODUCT]

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?

Thanks!

On Thu, 11 Oct 2007 23:47:18 -0700, blackpuppy <mingzhu.z [at] gmail.com>
wrote:

>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)

SELECT PRODUCT_ID, COMMENT, UPDATED_ON
FROM PRODUCT_COMMENT as A
WHERE UPDATED_ON =
(SELECT MAX(UPDATED_ON)
FROM PRODUCT_COMMENT as B
WHERE A.PRODUCT_ID = B.PRODUCT_ID)
GROUP BY PRODUCT_ID

Roy Harvey
Beacon Falls, CT

blackpuppy (mingzhu.z [at] 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 [at] sommarskog.se

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