SQL: improving join efficiency
If I turn this sub-query which selects sales persons and their highest price paid for any item they sell:

    select *,
        (select top 1 highestProductPrice
         from   orders o
         where  o.salespersonid = s.id
         order by highestProductPrice desc ) as highestProductPrice
    from salespersons s

in to this join in order to improve efficiency:

    select *, highestProductPrice
    from   salespersons s join (
           select salespersonid, highestProductPrice, row_number(
               partition by salespersonid 
               order by salespersonid, highestProductPrice) as rank
           from orders ) o on s.id = o.salespersonid

It still touches every order record.  However you cannot do this:

    select *, highestProductPrice
    from   salespersons s join (
           select salespersonid, highestProductPrice, row_number(
               partition by salespersonid 
               order by salespersonid, highestProductPrice) as rank
           from orders 
           where orders.salepersonid = s.id) o on s.id = o.salespersonid

The where clause in the join causes a `multi-part identifier "s.id" could not be bound.

**Is there any way to join the top 1 out of each order group with a join but without touching each record in orders?**
    
SQL: improving join efficiency
===

If I turn this sub-query which selects sales persons and their highest price paid for any item they sell:

    select *,
        (select top 1 highestProductPrice
         from   orders o
         where  o.salespersonid = s.id
         order by highestProductPrice desc ) as highestProductPrice
    from salespersons s

in to this join in order to improve efficiency:

    select *, highestProductPrice
    from   salespersons s join (
           select salespersonid, highestProductPrice, row_number(
               partition by salespersonid 
               order by salespersonid, highestProductPrice) as rank
           from orders ) o on s.id = o.salespersonid

It still touches every order record.  However you cannot do this:

    select *, highestProductPrice
    from   salespersons s join (
           select salespersonid, highestProductPrice, row_number(
               partition by salespersonid 
               order by salespersonid, highestProductPrice) as rank
           from orders 
           where orders.salepersonid = s.id) o on s.id = o.salespersonid

The where clause in the join causes a `multi-part identifier "s.id" could not be bound.

**Is there any way to join the top 1 out of each order group with a join but without touching each record in orders?**
    