SQLServer: analytic function – RowNumber()

Using AdventureWorks as before…

analfunc01

 

analfunc02

drop table t_product_persistent;
drop table t_product_staging;
select ProductModelID, Name, 'Persistent' as CatalogDescription into t_product_persistent
 FROM [AdventureWorks2014].[Production].[ProductModel]
select ProductModelID, Name, 'Staging' as CatalogDescription into t_product_staging
 FROM [AdventureWorks2014].[Production].[ProductModel]
-- delete all even keys from persistent
delete from t_product_persistent where ProductModelID%2 = 0
 select * from t_product_persistent order by ProductModelID
 select * from t_product_staging order by ProductModelID
-- Apply the rule: if the key exists on the persistent table, then use that row, else use the row on the 
-- staging table. Net expected result: the ProductModelId is unique across the combined set.
-- Small steps towards the goal...
-- Grab the superset from both tables
select * from (
 select p.ProductModelID, p.Name, p.CatalogDescription, 1 as TypePriority from t_product_persistent p
 union all
 select s.ProductModelID, s.Name, s.CatalogDescription, 2 as TypePriority from t_product_staging s
) x
order by ProductModelID
-- Grab the superset from both tables, adding a column to hold the ranking
select x.ProductModelId, x.name, x.CatalogDescription, x.TypePriority,
 ROW_NUMBER() over (partition by x.ProductModelId order by x.TypePriority) xx
from (
 select p.ProductModelID, p.Name, p.CatalogDescription, 1 as TypePriority from t_product_persistent p
 union all
 select s.ProductModelID, s.Name, s.CatalogDescription, 2 as TypePriority from t_product_staging s
) x
order by ProductModelID
-- Finally just keep the Priority 1s..
;with ModelWithPriority as (
 select x.ProductModelId, x.name, x.CatalogDescription, x.TypePriority,
 ROW_NUMBER() over (partition by x.ProductModelId order by x.TypePriority) xx
 from (
 select p.ProductModelID, p.Name, p.CatalogDescription, 1 as TypePriority from t_product_persistent p
 union all
 select s.ProductModelID, s.Name, s.CatalogDescription, 2 as TypePriority from t_product_staging s
 ) x
)
select * from ModelWithPriority where xx = 1
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s