Indexed views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Indexed views

Author

Topic

katehollington
Starting Member

5 Posts

Posted - 2008-04-03 : 06:13:55

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

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-04-03 : 06:52:44

Are you just trying to return the top maximum value of the resuly set?

katehollington
Starting Member

5 Posts

Posted - 2008-04-03 : 07:13:08

This view is then used in another query, which needs to see the job_id associated with the max js_id - if that makes sense? So I need both columns I'm afraid.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources