title: How to use conditions in SQLite (like if-statements, etc.)
body: <p>I have a table in a database with one column containing dates and another one containing scores. Basically, what I want to do is grab the best score in a given week.</p>
  
  <p>Weeks can start on any given day (From Friday to Thursday, for instance), and that is defined by the user. </p>
  
  <p>Here is what I have so far:</p>
  
  <pre><code>SELECT MAX(Series), DATE(DATE(Date, 'weekday 0'), '-7 days') dateStartOfWeek FROM SeriesScores
    WHERE Season = '2010-2011' 
    AND dateStartOfWeek = '2010-08-29'
    GROUP BY DateStartOfWeek
  </code></pre>
  
  <p>Where <code>Series</code> is the column containing the scores and <code>Date</code> is the (badly) named actual date.</p>
  
  <p>The problem with this query is that it works for every day except for the day the week is supposed to be starting on.</p>
  
  <p>For example: 2010-08-29 is a Sunday and in this example, I'm trying to find on which date the Sunday of the given week is. My function works for every day of that week except for 2010-08-29 (Sunday) since it tries to find the next day that is a Sunday (itself in this case). To compensate for that, I go back 7 days to get the correct Sunday, which creates the error for the already correct Sunday since this one doesn't need to go back 7 days or else it is one week off.</p>
  
  <p>I figured I could solve this problem easily using Java, but I want to see how it should be done using SQL instead.</p>
  
  <p>My solution (I don't even know if it can be done), would be to check if date and dateStartOfWeek are the same. If they are, don't substract 7 days from the date. If they're not, do as I did in my example. I don't know how to use conditions such as this one in SQL, though, and this is where I need help.</p>
  
  <p>Thanks a lot in advance!</p>