UsefulSQL/CTE_With_As.sql at master · rakemen/UsefulSQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
IF OBJECT_ID('tempdb..#SecurityValue') IS NOT NULL DROP TABLE #SecurityValue
CREATE TABLE #SecurityValue ( SecurityID INT, SecurityValueDate DATETIME, CustomerID BIGINT )

INSERT INTO #SecurityValue VALUES (100, '7/30/2015', 2013041201)
INSERT INTO #SecurityValue VALUES (100, '7/29/2015', 2013041201)
INSERT INTO #SecurityValue VALUES (100, '7/28/2015', 2013041201)
INSERT INTO #SecurityValue VALUES (200, '7/3/2015', 2013041201)
INSERT INTO #SecurityValue VALUES (200, '7/4/2015', 2013041201)
INSERT INTO #SecurityValue VALUES (200, '7/5/2015', 2013041201)
INSERT INTO #SecurityValue VALUES (300, '7/6/2015', 2013041201)
INSERT INTO #SecurityValue VALUES (300, '7/7/2015', 2013041201)
INSERT INTO #SecurityValue VALUES (400, '7/8/2015', 2013041201)

;WITH SecVals AS
(
	SELECT * FROM
		(SELECT SecurityID, SecurityValueDate, ROW_NUMBER() OVER (PARTITION BY SecurityID ORDER BY SecurityValueDate DESC) AS RowNumber
			FROM #SecurityValue WHERE CustomerID = 2013041201) sv
	WHERE RowNumber = 1
)
SELECT * FROM SecVals