Hi, I am able to get the TOP 1 for each group by using methods (CTE and Row_Number and Partition) that I have learned from this forum. Thanks everyone! To expand my arsenal, I am continuing to perfect my CROSS APPLY. I have seen CROSS APPLY used to achieve the same result. I have been playing with it but have not been successful. Can one of the seasonal veterans on here help me out? Thanks
--------------------------------------------------------
/*
OVERALL: Rigging Test Data
*/
DECLARE @tempTable TABLE
(
fGroup_ID TINYINT
, fDescription VARCHAR(20)
, fDate_Created DATETIME
);
INSERT INTO @tempTable
SELECT 1, NULL, GETDATE()
UNION ALL
SELECT 1, NULL, GETDATE()
UNION ALL
SELECT 2, NULL, GETDATE()
UNION ALL
SELECT 2, NULL, GETDATE()
UNION ALL
SELECT 3, NULL, GETDATE()
UNION ALL
SELECT 3, NULL, GETDATE()
--SELECT * FROM @tempTable
--------------------------------------------------------
;WITH cte AS
(
--PURPOSE: Apply Row_Number to the PARTITION (group)
SELECT
*
, ROW_NUMBER() OVER (PARTITION BY fGroup_ID ORDER BY fDate_Created) AS My_Row_ID
FROM @tempTable
)
--PURPOSE: Filter out the CTE
SELECT * FROM cte
WHERE My_Row_ID = 1