SQL Server How to return one records from each group

Question

Please See the code

CREATE TABLE [dbo].[Test](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Ticker] [varchar](20) NULL,
 [ClientCode] [varchar](20) NULL,
 [Earnings] [varchar](20) NULL,
 [PrePost] [varchar](20) NULL,
 [LastCSMDeliveredDate] [datetime] NULL
) ON [PRIMARY]
GO

Insert into Test(Ticker,ClientCode,Earnings,PrePost,LastCSMDeliveredDate)
values('ABB','ABB','3Q2021','Pre',GetDate()-5),
('ABB','ADS','2Q2021','Pre',GetDate()-10),
('ABB','ABB','2Q2021','Pre',GetDate()-12),

('ANTM','IVZ','3Q2021','Pre',GetDate()-5),
('ANTM','IVZ','2Q2021','Pre',GetDate()-11),
('ANTM','IVZ','3Q2021','Pre',GetDate()-15),

('CSGP','IVZ','3Q2021','Pre',GetDate()-5),
('CSGP','DG','2Q2021','Pre',GetDate()-12),
('CSGP','IVZ','3Q2021','Pre',GetDate()-20)


Select top 1 Ticker,ClientCode,Max(Earnings) Earnings,Max(PrePost) AS PrePost,Max(LastCSMDeliveredDate) as LastCSMDeliveredDate from test
    Group BY Ticker,ClientCode
    Order BY Ticker,LastCSMDeliveredDate DESC

Here i am grouping on Ticker,ClientCode and order data by LastCSMDeliveredDate DESC.

when i am executing this query then one records is coming but i want to return one records from each group whose LastCSMDeliveredDate is latest one.

please guide me what to change in my code. thanks

Answer 1

This issue resolved.

select /*top 1*/ Ticker,ClientCode,Max(Earnings) Earnings,Max(PrePost) AS 
PrePost,Max(LastCSMDeliveredDate) as LastCSMDeliveredDate from test
Group BY Ticker,ClientCode
Order BY Ticker,LastCSMDeliveredDate DESC

OR

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY Ticker,ClientCode ORDER BY LastCSMDeliveredDate DESC) AS rn
   FROM test
)
SELECT *
FROM cte
WHERE rn = 1