title: How to get biggest value from 2 or more fields in a subquery
body: <p>I have a table with customers that I join with a fact table with sales, based on invoices.</p>
  <p>What I need from my report is to get in first part the biggest value of sales based on the incoming order type (1,2,3,C,D) for a customer for last year. And in the second part to get the same but for current year. What I get as result from my current query is all incoming order types with the customer revenue made for each of them. I tried with outer apply as subquery to get only the top 1 value ordered by revenue descending, but in the result I get the same - For all order types the customer revenue. Please help! I hope my explanation isn't understood only by me (happens a lot..)</p>
  <pre><code>  use dwh01;
  WITH OrderTypeUsedLY AS
  (
  SELECT 
       c.CustomerKey
      ,c.BranchId
      ,c.CustomerId
      ,c.CustomerName
      ,ISNULL(SUM(y.[Sale_Revenue]), 0) as [Sale_Revenue_LY]
      ,ISNULL(SUM(y.[Sale_GrossMarginTotal]), 0) as [Sale_GrossMarginTotal_LY] 
      ,y.IncomingOrderTypeId as IncomingOrderType_LY
  FROM live.DimCustomer c
  left join (SELECT s.CustomerKey,iot.IncomingOrderTypeid, s.[Sale_Revenue], s.[Sale_GrossMarginTotal] FROM [dwh01].[live].[FactSales] s 
  inner join live.DimDate d
      on d.DateId = s.PostingDateKey
  inner join live.DimIncomingOrderType iot on iot.IncomingOrderTypeKey = s.IncomingOrderTypeKey
  where s.ReportCurrencyId = 'LC'      
  and D.Year = YEAR(GETDATE())-1   --- Last Year            
  ) y on c.CustomerKey = y.CustomerKey             
  where c.CustomerKey = '157053'
  group by c.CustomerKey, c.CustomerId, c.CustomerName, c.BranchId, y.IncomingOrderTypeId
  ),
  --*********************************************************************************************************************************--
  OrderTypeCY as(
  SELECT 
       c.CustomerKey
      ,c.BranchId
      ,c.SalesRepKey
      ,c.CustomerId
      ,c.CustomerName
      ,ISNULL(SUM(y.[Sale_Revenue]), 0) as [Sale_Revenue_CY]
      ,ISNULL(SUM(y.[Sale_GrossMarginTotal]), 0) as [Sale_GrossMarginTotal_CY] 
      ,y.IncomingOrderTypeId as IncomingOrderType_CY
  FROM live.DimCustomer c
  left join (SELECT s.CustomerKey,iot.IncomingOrderTypeid, s.[Sale_Revenue], s.[Sale_GrossMarginTotal] FROM [dwh01].[live].[FactSales] s 
  inner join live.DimDate d
      on d.DateId = s.PostingDateKey
  inner join live.DimIncomingOrderType iot on iot.IncomingOrderTypeKey = s.IncomingOrderTypeKey
  where s.ReportCurrencyId = 'LC'      
  and D.Year = YEAR(GETDATE())   --- Current Year           
  ) y on c.CustomerKey = y.CustomerKey             
  where c.CustomerKey = '157053'
  group by c.CustomerKey, c.CustomerId, c.CustomerName, c.BranchId, y.IncomingOrderTypeId, c.SalesRepKey
  )
  --*********************************************************************************************************************************--
  SELECT
      otly.BranchId,
      rep.SalesRepId,
      rep.SalesRepName,
      otly.CustomerId,
      otly.CustomerName,
      otly.Sale_Revenue_LY,
      otly.Sale_GrossMarginTotal_LY,
      IncomingOrderType_LY,
      otcy.Sale_Revenue_CY,
      otcy.Sale_GrossMarginTotal_CY,
      IncomingOrderType_CY
  from OrderTypeUsedLY otly
  left join OrderTypeCY otcy 
      on otly.CustomerKey = otcy.CustomerKey 
  join live.DimCustomer cus on cus.CustomerKey = otcy.CustomerKey
  join live.DimSalesRep rep on rep.SalesRepKey = otcy.SalesRepKey
  order by otcy.Sale_Revenue_CY desc, otly.Sale_Revenue_LY desc
  ,rep.SalesRepId
  </code></pre>
  <p>And here is the outer apply I tried:</p>
  <pre><code>outer apply (
          SELECT top 1
      iot.IncomingOrderTypeId,
      Sale_Revenue
      FROM [dwh01].[live].DimIncomingOrderType iot
      where iot.IncomingOrderTypeKey = y.IncomingOrderTypeKey
      order by Sale_Revenue desc) x
  </code></pre>
  <p>In the first select ( with OrderTypeUsed_LY ) I get this:</p>
  <p><a href="https://i.stack.imgur.com/00zOG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/00zOG.png" alt="First part of query" /></a></p>
  <p>And I get the same in the second select, but with the values for current year.</p>
  <p>The purpose of the report is to see the difference in the incoming order type most used (most profit made with it) for a customer last year and to see if he continues to use it this year, or uses another incoming order type this year.</p>
  <p>Again I'm sorry for the bad explanation, I'm trying my best (I understand myself very well)</p>
  <p>Here is the expected result:
  <a href="https://i.stack.imgur.com/42SrB.png" rel="nofollow noreferrer">Expected Result</a></p>
  <p>I marked in red the last year part and in green the current year part.</p>