Posted by blackpuppy on 10/12/07 06:47

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!

[Back to original message]