How can i get a ruled data to my select view?

Transact-SQL
1

Hi everyone, i have a problem to transact a select view on SQL. I have two main table; let's say them A and B. I get names from table A and get max(DATE) from table B with join.

Now i want to get data from table B but they have to be max(DATE)'s line(I mean i want to get last date's data).

how can i get this data from my select view?

If i make mistakes to express my problem, sorry for. English is my second language.

2

In future, please post DDL and test data:

CREATE TABLE #A
(
	aID int NOT NULL
		PRIMARY KEY
	,aName varchar(20) NOT NULL
);
GO
INSERT INTO #A
VALUES (1, 'Name1')
	,(2, 'Name2');
GO
CREATE TABLE #B
(
	aID int NOT NULL
	,bDate date NOT NULL
	,AnotherColumn varchar(20) NOT NULL
	,PRIMARY KEY (aID, bDate)
);
GO
INSERT INTO #B
VALUES (1, '20250101', 'Rubbish 1')
	,(1, '20250102', 'Rubbish 2')
	,(1, '20250103', 'Rubbish 3')
	,(2, '20250102', 'Rubbish 1')
	,(2, '20250110', 'Rubbish 2');
GO

One approach is to use ROW_NUMBER():

WITH NewB
AS
(
	SELECT aID, bDate, AnotherColumn
		,ROW_NUMBER() OVER (PARTITION BY aID ORDER BY bDate DESC) AS rn
	FROM #B
)
SELECT A.aID, A.aName, B.bDate, B.AnotherColumn
FROM #A A
	JOIN NewB B
		ON A.aID = B.aID
WHERE B.rn = 1;