Hmmm.... replacing Having with Top 1...order by causes it to return Nulls for the max() for some rows -- not what I want. Here are the execution plans (couldn't see how to attach a document -- did I miss it?):

Join:

|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([nco].[STATUS_ID])=(.[STATUS_ID]), RESIDUAL:([OR_NCCORE].[dbo].[ORDER_DIM].[STATUS_ID] as [nco].[STATUS_ID]=[OR_NCCORE].[dbo].[STATUS_DIM].[STATUS_ID] as .[STATUS_ID]))

|--Sort(ORDER BY:([nco].[STATUS_ID] ASC))

| |--Hash Match(Inner Join, HASH:([OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[ORDER_ID])=([nco].[ORDER_ID]), RESIDUAL:([OR_NCCORE].[dbo].[ORDER_DIM].[ORDER_ID] as [nco].[ORDER_ID]=[OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[ORDER_ID]))

| |--Filter(WHERE:([Expr1010]=(1)))

| | |--Sequence Project(DEFINE:([Expr1010]=row_number))

| | |--Segment

| | |--Sort(ORDER BY:([OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[ORDER_ID] ASC, [OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[ORDER_ITEM_ID] ASC))

| | |--Table Scan(OBJECT:([OR_NCCORE].[dbo].[ORDER_ITEM_DIM]))

| |--Table Scan(OBJECT:([OR_NCCORE].[dbo].[ORDER_DIM] AS [nco]))

|--Sort(ORDER BY:(.[STATUS_ID] ASC))

|--Table Scan(OBJECT:([OR_NCCORE].[dbo].[STATUS_DIM] AS ))

Cross Apply:

|--Nested Loops(Inner Join, OUTER REFERENCES:([nco].[ORDER_ID]))

|--Hash Match(Inner Join, HASH:(.[STATUS_ID])=([nco].[STATUS_ID]), RESIDUAL:([OR_NCCORE].[dbo].[STATUS_DIM].[STATUS_ID] as .[STATUS_ID]=[OR_NCCORE].[dbo].[ORDER_DIM].[STATUS_ID] as [nco].[STATUS_ID]))

| |--Table Scan(OBJECT:([OR_NCCORE].[dbo].[STATUS_DIM] AS ))

| |--Table Scan(OBJECT:([OR_NCCORE].[dbo].[ORDER_DIM] AS [nco]))

|--Filter(WHERE:([Expr1010] IS NOT NULL))

|--Stream Aggregate(DEFINE:([Expr1010]=MAX([OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[ORDER_ITEM_ID])))

|--Index Spool(SEEK:([OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[ORDER_ID]=[OR_NCCORE].[dbo].[ORDER_DIM].[ORDER_ID] as [nco].[ORDER_ID]))

|--Index Scan(OBJECT:([OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[IX_ORDER_ITEM_ID_with_Includes]))