Indexed views

[katehol]

Hi,

I am trying to create an indexed view, but because I am using a MAX function, I get the error

Cannot create index on view "dbo.View" because it uses aggregate "MAX". Consider eliminating the aggregate, not indexing the view, or using alternate aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT, substitute COUNT_BIG.

Am totally stuck on how I can replace the MAX function.

Any help would be appreciated.

SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO

CREATE VIEW [dbo].[View]
WITH SCHEMABINDING
AS

SELECT     TOP 100 PERCENT MAX(js_id) AS job_event, job_id
FROM         dbo.JobEvent
GROUP BY job_id
ORDER BY job_event
GO

CREATE UNIQUE CLUSTERED INDEX IX_VMaxJobEvent ON View (job_id)

Thanks

 

[foxdev]

Katehol, I did some research (I'm sure you did as well), and two things seem clear: this is a common complaint, and there is no workaround (at least, none acknowledged by MS). Specifically for MAX and MIN, there aren't alternatives like there are for AVG.

--------------

http://www.liquidsql.com

SQLS metasearch

 

[katehol]

Yeah, thanks. I did do a fair amount of research too. I was just hoping that I had missed something!
Thanks anyway.
K