title: How to get max previous row in case of duplicated rows and with priority of each max column taken
body: <p>I have a table that contains rows of accounts and details about them. I've created a temporary table that fits my conditions, and called it #temp2. Now what I need to do is to take the max previous row from the original table that fits other conditions different from the current row that has been taken to the temporary table.
  The problem I'm dealing with is the way I need to take the max previous row. The simple way if if there are duplicated rows for the account, the max previous row would be on the max date column. If the duplicated rows have the same date as well, I need to take the max Code column, And if there are rows with duplicated date, and Code columns for the account, then I would take the Amount column. Priority of max previous row for the account is that - date, Code, Amount.
  An example of an account with previous rows that I need to take the max:</p>
  <pre><code>       **Account**     **Date**    **Code**    **Amount** 
  1.        123           31/05/20       6           200  
  2.        123           31/05/20       6           200
  3.        123           31/05/20       6           300
  4.        124           31/05/20       7           100
  5.        124           31/05/20       8            90
  </code></pre>
  <p>The expected results for account 123 is row 3 and for account 124 is row 5.
  The current row of 30/06/20 for each account is either emitted from #temp table or included depending on different columns conditions.
  Snippet of my approach:</p>
  <pre><code>SELECT  account, max(Datecolumn) , max(Code) , max(Amount) --,  more columns...
  FROM Mytable
  WHERE Datecolumn  &lt;   ( select  max(Datecolumn)  from #temp )
  and Code          &lt;   ( select  max(Code)        from #temp  )
  and Amount        &lt;   ( select  max(Amount)      from #temp )
  -- different conditions for different columns....
  GROUP BY  account  --, more columns...
  </code></pre>