CROSS APPLY to get TOP 1 of each Group

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

Hi

this is my solution

I am not expert

SQL
SELECT * FROM (SELECT 1 as abc) D 
CROSS APPLY 
   ( 
   select * 
   from ( SELECT *, ROW_NUMBER() OVER (PARTITION BY fGroup_ID ORDER BY fDate_Created) AS My_Row_ID from #tempTable) E
   WHERE E.My_Row_ID = D.abc
   ) A 
GO
1 Like