OnTopic-Library/OnTopic.Data.Sql.Database/Views/ExtendedAttributeIndex.sql at master · OnTopicCMS/OnTopic-Library · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
--------------------------------------------------------------------------------------------------------------------------------
-- EXTENDED ATTRIBUTES (INDEX)
--------------------------------------------------------------------------------------------------------------------------------
-- Filters the Extended Attributes table by the latest version for each topic. For most use cases, this should be the primary
-- source for retrieving extended attributes, since it excludes historical versions.
--------------------------------------------------------------------------------------------------------------------------------
CREATE
VIEW	[dbo].[ExtendedAttributeIndex]
WITH	SCHEMABINDING
AS

WITH	TopicExtendedAttributes AS (
  SELECT	TopicID,
	AttributesXml,
	Version,
	RowNumber = ROW_NUMBER() OVER (
	  PARTITION BY		TopicID
	  ORDER BY		Version		DESC
	)
  FROM	[dbo].[ExtendedAttributes]
)
SELECT	TopicID,
	AttributesXml,
	Version
FROM	TopicExtendedAttributes
WHERE	RowNumber		= 1