Alternative way to write a query

Transact-SQL
1

I am trying to replace outer apply with left join or any other alternatives.Please help

select * from test t
outer apply
(
select top 1 LoanNumber from LoanActivity la where la.instid=t.instid order by dtCreated asc)
t;

Thanks in advance!

2

Try this:

select * /* You should always specify the fields you need, and not "select *" */
  from test as t
       left outer join (select instid
                              ,min(loannumber) as loannumber
                          from loanactivity
                         group by instid
                       ) as l
                    on l.instid=t.instid
;

or this:

with cte
  as (select instid
            ,min(loannumber) as loannumber
        from loanactivity
       group by instid
     )
select * /* You should always specify the fields you need, and not "select *" */
  from test as t
       left outer join cte as l
                    on l.instid=t.instid
;

Now, if you want more fields from loadactivity table, do this:

select * /* You should always specify the fields you need, and not "select *" */
  from test as t
       left outer join (select * /* You should always specify the fields you need, and not "select *" */
                              ,row_number() over(partition by instid
                                                 order by dtcreated
                                                )
                               as rn
                          from loanactivity
                       ) as l
                    on l.instid=t.instid
                   and l.rn=1
;

or this:

with cte
  as (select * /* You should always specify the fields you need, and not "select *" */
            ,row_number() over(partition by instid
                               order by dtcreated
                              )
             as rn
        from loanactivity
     )
select * /* You should always specify the fields you need, and not "select *" */
  from test as t
       left outer join cte as l
                    on l.instid=t.instid
                   and l.rn=1
;
3

Thanks Superb, that works.