View with CTE : To eliminate duplicate rows in View : SQL Server 2008

Sample Table Structure

CREATE TABLE [dbo].[SalesHistory]
(
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO

Sample Data with duplication
INSERT INTO SalesHistory(Product, SaleDate, SalePrice) values
('Computer','1919-03-18 00:00:00.000',1008.00),
('BigScreen','1927-03-18 00:00:00.000',91.00),
('PoolTable','1927-04-01 00:00:00.000',139.00),
('Computer','1919-03-18 00:00:00.000',1008.00),
('BigScreen','1927-03-25 00:00:00.000',92.00'),
('PoolTable','1927-03-25 00:00:00.000',108.00),
('Computer','1919-04-01 00:00:00.000',150.00),
('BigScreen','1927-04-01 00:00:00.000', 123.00),
('PoolTable','1927-04-01 00:00:00.000', 139.00)
('Computer','1919-04-08 00:00:00.000', 168.00)


View with CTE : To eliminate duplicate rows in View : SQL Server 2008

WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)
AS
(
SELECT
Product, SaleDate, SalePrice,
Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC)
FROM SalesHistory

)

select * from SalesCTE where Ranking not in (Select Ranking FROM SalesCTE WHERE Ranking > 1)

No comments: