title: MySQL what is the purpose of MAX(IF()) in these queries?
body: <p>this is my first question asked here.</p>
  <p>I'm relatively new to SQL and have seen a few times the usage of MAX(IF(condition, value1, value2)) in which value1 and/or value2 are not columns and I just cant understand what it is doing. From my understanding, the MAX function returns a single value which is the biggest value in a column or set of values(which can be interpreted as a column) but in these situations the values given to the MAX() function are not always columns and the utility of the MAX function just doesn't seem clear to me, especially since the output is often being taken as a column.</p>
  <p>here are a few examples of the usage I don't understand:</p>
  <p>EX1: the one seen in here-&gt;
  <a href="https://stackoverflow.com/questions/47419539/performance-in-mysql-max-if-condition-or-subquery-condition">Performance in MYSQL , MAX( IF( condition ) ) or Subquery condition</a></p>
  <p>EX2:</p>
  <pre><code>SELECT
     id_candidate,
     MAX(IF(situation = 'hired', '9999-01-01', '9999-01-02') AS date
  FROM
     processes
  </code></pre>
  <p>I have tested a bit to see if i could understand but a few things got me even more confused:</p>
  <p>1- if a single value is given as a column, the returned table will contain a column in which all rows have the value given;</p>
  <p>2- but if MAX('value') is given as a column such as &quot;date&quot;, for example, a single row will be returned with the value given in the date column and no clear relation between the row and the fact that it was chosen over any other, but the row returned is always the same as long as the value is the same.</p>
  <p>I have found no material explaining this and I don't know if I'm supposed to already know this or what but I'm just completely lost here.</p>