Rusty Mind Seeks Query Help

Transact-SQL
1

Greetings,

My table holds subscription info for customers. I only need the [top 1, latest date (SubscriptionEndDate) desc], of each unique SubscriptionID.

I would like to select only row 1 and 3 from the ALL DATA below.

ALL DATA

Id            CustID         SubscriptionID           SubscriptionEndDate
------------- -------------- ------------------------ ---------------------------
6c09feab-15b2 LwrL6rQq3IQ7Uh 1LExeeDJA442s3zPTOj7g2Q0 2022-03-26 15:41:53.0000000
885f5527-64ad LwrL6rQq3IQ7Uh 1LExeeDJA442s3zPTOj7g2Q0 2022-02-26 15:41:53.0000000
fb6af4c4-2c64 LwrL6rQq3IQ7Uh 5LExeeDJA442s3zPTOj7g2Q1 2022-03-26 15:41:53.0000000
6c09feab-15b2 LwrL6rQq3IQ7Uh 5LExeeDJA442s3zPTOj7g2Q1 2022-02-26 15:41:53.0000000

MY DESIRED RESULT

Id            CustID         SubscriptionID           SubscriptionEndDate
------------- -------------- ------------------------ ---------------------------
6c09feab-15b2 LwrL6rQq3IQ7Uh 1LExeeDJA442s3zPTOj7g2Q0 2022-03-26 15:41:53.0000000
fb6af4c4-2c64 LwrL6rQq3IQ7Uh 5LExeeDJA442s3zPTOj7g2Q1 2022-03-26 15:41:53.0000000

Please use Test Data Here:

CREATE TABLE #Subscriptions(
	[Id] [nvarchar](13) NOT NULL,
	[CustID] [nvarchar](14) NOT NULL,
	[SubscriptionID] [nvarchar](24) NULL,
	[SubscriptionEndDate] [datetime2](7) NULL,
)

Insert Into #Subscriptions (Id, CustID, SubscriptionID, SubscriptionEndDate)
Values ('6c09feab-15b2', 'LwrL6rQq3IQ7Uh', '1LExeeDJA442s3zPTOj7g2Q0', '2022-03-26 15:41:53.0000000'),
       ('885f5527-64ad', 'LwrL6rQq3IQ7Uh', '1LExeeDJA442s3zPTOj7g2Q0', '2022-02-26 15:41:53.0000000'),
       ('fb6af4c4-2c64', 'LwrL6rQq3IQ7Uh', '5LExeeDJA442s3zPTOj7g2Q1', '2022-03-26 15:41:53.0000000'),
       ('6c09feab-15b2', 'LwrL6rQq3IQ7Uh', '5LExeeDJA442s3zPTOj7g2Q1', '2022-02-26 15:41:53.0000000')

Select *
From #Subscriptions

Drop Table #Subscriptions

Thank you so much for your help!

JB

2

You can use row_number for this:

SELECT 
	tbl.Id,
	tbl.CustID, 
	tbl.SubscriptionID,
	tbl.SubscriptionEndDate 
FROM
(
SELECT
	'RowNumber'= ROW_NUMBER() OVER (PARTITION BY CustID, SubscriptionID ORDER BY SubscriptionEndDate DESC), 
	S.Id,
	S.CustID, 
	S.SubscriptionID,
	S.SubscriptionEndDate 
FROM #Subscriptions S
) tbl
WHERE RowNumber=1;
3

Thank you RogierPronk!

Really appreciated.

JB