Select the first row in a join of two tables in one statement

Question

I need to select only the first row from a query that joins tables A and B. On table B exist multiple records with same name. There are not identifiers in any of the two tables. I cannot change the scheme either because I do not own the DB.

TABLE A
NAME

TABLE B
NAME
DATA1
DATA2

Select Distinct A.NAME,B.DATA1,B.DATA2 
From A 
Inner Join B on A.NAME = B.NAME

This gives me

NAME       DATA1    DATA2
sameName   1        2
sameName   1        3
otherName  5        7
otherName  8        9

but I need to retrieve only one row per name

NAME       DATA1    DATA2
sameName   1        2
otherName  5        7

I was able to do this by adding the result into a temp table with a identity column and then select the minimum id per name.

The problem here is that I require to do this in one single statement.

Answer 1

This will work:

with temp as (
    select A.NAME, B.DATA1, B.DATA2, 
        row_number() over (partition by A.NAME order by A.NAME) as rownum
    from TABLEA A inner join TABLEB B
    on A.NAME = B.NAME
)
select NAME, DATA1, DATA2 from temp where rownum = 1

If you want to select the least value of data1 and within it data2, then use this variation:

with temp as (
    select A.NAME, B.DATA1, B.DATA2, 
        row_number() over (partition by A.NAME order by B.DATA1, B.DATA2) as rownum
    from TABLEA A inner join TABLEB B
    on A.NAME = B.NAME
)
select NAME, DATA1, DATA2 from temp where rownum = 1

Both the queries will give one row per name.