tSQL Return first row of partition

The goal is to return the first row of a partition using as little resources as possible. So far, I've seen recommendations to use:

with customerinfo_CTE as
(
select row_number() over(partition by customer order by year desc) as RNumber, *
from customerInfo
)

select * 
from customerinfo_CTE 
where RNumber = 1

So, what I have is a customerInfo table where we keep track of past customer details (IE: contact info, company names, etc). I'm using a CTE at this point, but could just as well use a #Temp Table or @Variable Table.

I'm hoping there's a way to improve performance by getting the only the first record for the year in the CTE (or whatever) prior to using it in a join or other portions of the script.

Right now, I'm using:

with customerinfo_CTE as
(
select row_number() over(partition by customer order by year desc) as RNumber, *
from customerInfo
)

select * 
from customerOrders a
inner join  customerinfo_CTE b
on a.CustomerID = b.CustomerID
where b.RNumber = 1

While this gives me the results I want, I'm hoping there's a way to slim it down to just get the first result in the CTE declaration without having to filter on the backend in the where clause for the join.

Thanks in advance.

Solution

The cross apply uses a inline view to get the associated info records to orders but using cross apply operator (not really a join), it does it for EACH record in customerOrders returning the TOP 1 record for each customer based on the order defined in the inline view.

SELECT * 
FROM CUSTOMERORDERS A
CROSS APPLY (SELECT top 1 * 
             FROM customerInfo B
             WHERE A.CustomerID = B.customerID
             ORDER by year desc) C

though the tables based on name seem backwards to me; but based on your example seem correct.

I can see lots of orders but 1 info record... so I would think you'd want the top order by year of orders... but maybe not.

So maybe you mean this?

SELECT * 
FROM CustomerInfo A
CROSS APPLY (SELECT top 1 * 
             FROM CUSTOMERORDERS B
             WHERE A.CustomerID = B.customerID
             ORDER by year desc) C

but maybe you have in info record for each customer every year... so maybe not..

And a related article explaining apply usage along with some good examples and why performance can be boosted: https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/