title: How do I retrieve a row with max/min column based on a condition from a set of grouped data
body: <p>I have a table with the following structure and sample data:</p>
  
  <pre><code>id | date         | nutrient_number | nutrient_goal_value
  ---------------------------------------------------------
  1  | "2019-01-01" | 1               | 5
  2  | "2019-01-05" | 1               | 6
  3  | "2019-01-06" | 1               | 7
  4  | "2019-01-01" | 2               | 20
  5  | "2019-01-05" | 2               | 25
  6  | "2019-01-06" | 3               | 10
  7  | "2019-01-10" | 3               | 8
  </code></pre>
  
  <p>I need to retrieve a single row per nutrient_number, so in this example I would need 3 rows to be returned. My thinking is I would group by nutrient_number.</p>
  
  <pre><code>SELECT * from nutrient_goal_table GROUP BY nutrient_number
  </code></pre>
  
  <p>But, the returned row needs to be the row with the <strong>maximum date</strong> <em>before or ==</em> the date I query with (if it exists), and if it doesn't exist then it should be the row with the <strong>minimum date</strong> <em>after</em> the date I'm querying with. </p>
  
  <p>I could get this to work for one of the conditions, like so:</p>
  
  <pre><code>SELECT id, MAX(date), nutrient_number, nutrient_goal_value
  FROM nutrient_goal_table
  WHERE date &lt;= '2019-01-06'
  GROUP BY nutrient_number
  </code></pre>
  
  <p>This returns:</p>
  
  <pre><code>id | date         | nutrient_number | nutrient_goal_value
  ---------------------------------------------------------
  3  | "2019-01-06" | 1               | 7
  5  | "2019-01-05" | 2               | 25
  6  | "2019-01-06" | 3               | 10
  </code></pre>
  
  <p>which is fine. However, if I had to do a query like the following:</p>
  
  <pre><code>SELECT id, MAX(date), nutrient_number, nutrient_goal_value
  FROM nutrient_goal_table
  WHERE date &lt;= '2019-01-05'
  GROUP BY nutrient_number
  </code></pre>
  
  <p>I would obviously only get 2 results, when I need there to be 3. </p>
  
  <pre><code>id | date         | nutrient_number | nutrient_goal_value
  ---------------------------------------------------------
  2  | "2019-01-05" | 1               | 6
  5  | "2019-01-05" | 2               | 25
  </code></pre>
  
  <p>To get the third result, I would need the above query to fetch the closest date past the date I supply i.e. the minimum date which is greater than the supplied date, but it should only do so if no rows exist that have a date &lt;= the supplied date.</p>
  
  <p>The result set I need would look like this with a supplied date of '2019-01-05'</p>
  
  <pre><code>id | date         | nutrient_number | nutrient_goal_value
  ---------------------------------------------------------
  2  | "2019-01-05" | 1               | 6
  5  | "2019-01-05" | 2               | 25
  6  | "2019-01-06" | 3               | 10
  </code></pre>
  
  <p>I've been racking my brain for a while now, but I'm not very experienced and I'm struggling to come up with a solution. Could anyone point me in the right direction?</p>