title: Complex RANK in SQL
body: <p>I have a complex query which is really over the top of my head.
  I think RANK of the RANK-ing is needed, but there must be a better, and an existing way.</p>
  
  <p>Here I have a simpe table:</p>
  
  <pre><code>Manufacturer    DateOF  Status              Prefer
  Dell            05-2014 ComputerInstalled   30
  Dell            05-2014 ComputerUninstalled 70
  Dell            05-2014 ComputerUninstalled 70
  Dell            05-2014 ComputerUninstalled 70
  Dell            05-2014 ComputerInstalled   30
  Dell            05-2014 ComputerUninstalled 70
  Dell            05-2014 ComputerNew         26
  Dell            05-2014 ComputerNew         26
  Dell            05-2014 ComputerInstalled   30
  Dell            05-2014 ComputerInstalled   30
  </code></pre>
  
  <p>What I need to do is to GROUP BY the table by MANUFACTURER and DATEOF columns,
  then choose the rows with the lowest PREFER number (26 in this case).</p>
  
  <p>Its easy with RANK function:</p>
  
  <pre><code>SELECT sq.*
  FROM
  (
  SELECT
  *,
  RANK() OVER (PARTITION BY Manufacturer,DateOF ORDER BY Prefer) AS RankPrefer
  FROM
  table1
  WHERE
  RankPrefer = 1
  ) sq
  </code></pre>
  
  <p>So I will have the result of 2 rows with Status ComputerNew.</p>
  
  <pre><code>Manufacturer    DateOF  Status              Prefer
  Dell            05-2014 ComputerNew         26
  Dell            05-2014 ComputerNew         26
  </code></pre>
  
  <p>Thats easy, and not the question.</p>
  
  <p><strong>The question is:</strong></p>
  
  <p>I have to implement the following rule:</p>
  
  <p>If the rows with the lowest <strong>Prefer</strong> values (e.g.: 26)
  turn out to have <strong>ComputerNew</strong> value in their <strong>Status</strong> field,
  then I have to include more rows with <strong>ComputerInstalled</strong> values.</p>
  
  <p>The result should be this:</p>
  
  <pre><code>Manufacturer    DateOF  Status              Prefer
  Dell            05-2014 ComputerInstalled   30
  Dell            05-2014 ComputerInstalled   30
  Dell            05-2014 ComputerNew         26
  Dell            05-2014 ComputerNew         26
  Dell            05-2014 ComputerInstalled   30
  Dell            05-2014 ComputerInstalled   30
  </code></pre>
  
  <p>Similar to this rule, I have one more:</p>
  
  <p>If the rows with the lowest <strong>Prefer</strong> values (e.g.: 26)
  turn out to have <strong>ComputerOld</strong> value in their <strong>Status</strong> field,
  then I have to include more rows with <strong>ComputerUninstalled</strong> values.</p>
  
  <p>I think RANK of RANKING would solve this, but now I am really lost.</p>
  
  <p>Any help is appreciated on this riddle.</p>
  
  <p>Thank you</p>
  
  <hr>
  
  <p><strong>Edit1:</strong></p>
  
  <p>Gordon's solution is almost good, but not perfect.</p>
  
  <p>I give you more test data, there you can see where it fails.
  SQLFiddle to test is <a href="http://sqlfiddle.com/#!3/527f6/1" rel="nofollow">here</a>.</p>
  
  <p>I include the test data here as well:</p>
  
  <pre><code>INSERT Table1 VALUES ('HP10011','04/01/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP10011','04/04/2014','ComputerOld',26)
  INSERT Table1 VALUES ('HP10011','04/04/2014','ComputerOld',26)
  INSERT Table1 VALUES ('HP10011','04/30/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP10011','05/23/2014','QuickDispose',10)
  INSERT Table1 VALUES ('HP10011','06/03/2014','QuickDispose',10)
  INSERT Table1 VALUES ('HP10077','04/01/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP1910','04/25/2014','QuickDispose',10)
  INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerInstalled',30)
  INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerInstalled',30)
  INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerInstalled',30)
  INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerInstalled',30)
  INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP1910','05/01/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP1910','05/02/2014','ComputerInstalled',30)
  INSERT Table1 VALUES ('HP1910','05/02/2014','ComputerInstalled',30)
  INSERT Table1 VALUES ('HP3720','05/07/2014','ComputerInstalled',30)
  INSERT Table1 VALUES ('HP3720','05/07/2014','ComputerInstalled',30)
  INSERT Table1 VALUES ('HP3720','05/07/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP3720','05/07/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP3720','05/07/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP3720','05/07/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP3720','05/08/2014','ComputerInstalled',30)
  INSERT Table1 VALUES ('HP3720','05/08/2014','ComputerInstalled',30)
  INSERT Table1 VALUES ('HP3720','05/08/2014','ComputerInstalled',30)
  INSERT Table1 VALUES ('HP3720','05/08/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP3720','06/06/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP3720','06/06/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP3720','06/10/2014','ComputerOld',26)
  INSERT Table1 VALUES ('HP3720','06/10/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP3720','06/10/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP3720','06/11/2014','ComputerOld',26)
  INSERT Table1 VALUES ('HP3720','06/11/2014','ComputerUninstalled',70)
  INSERT Table1 VALUES ('HP3720','06/11/2014','ComputerUninstalled',70)
  </code></pre>
  
  <p>The query returns both rows ComputerInstalled and ComputerUninstalled
  for the following data:</p>
  
  <pre><code>'HP1910','05/01/2014','ComputerInstalled',30
  'HP1910','05/01/2014','ComputerUninstalled',70
  </code></pre>
  
  <p>It should choose ComputerInstalled only, because for that Manufacturer, in the same month, it should choose the lowest Prefer (30).</p>
  
  <p>The result for this dataset should be this:</p>
  
  <pre><code>Manufacturer    DateOF  Status          Prefer
  HP10011 2014-04-01  ComputerUninstalled 70
  HP10011 2014-04-04  ComputerOld         26
  HP10011 2014-04-04  ComputerOld         26
  HP10011 2014-04-30  ComputerUninstalled 70
  HP10011 2014-05-23  QuickDispose        10
  HP10011 2014-06-03  QuickDispose        10
  HP10077 2014-04-01  ComputerUninstalled 70
  HP1910  2014-04-25  QuickDispose        10
  HP1910  2014-05-01  ComputerInstalled   30
  HP1910  2014-05-01  ComputerInstalled   30
  HP1910  2014-05-01  ComputerInstalled   30
  HP1910  2014-05-01  ComputerInstalled   30
  HP3720  2014-05-07  ComputerInstalled   30
  HP3720  2014-05-07  ComputerInstalled   30
  HP3720  2014-05-08  ComputerInstalled   30
  HP3720  2014-05-08  ComputerInstalled   30
  HP3720  2014-05-08  ComputerInstalled   30
  HP3720  2014-06-06  ComputerUninstalled 70
  HP3720  2014-06-06  ComputerUninstalled 70
  HP3720  2014-06-10  ComputerOld         26
  HP3720  2014-06-10  ComputerUninstalled 70
  HP3720  2014-06-10  ComputerUninstalled 70
  HP3720  2014-06-11  ComputerOld         26
  HP3720  2014-06-11  ComputerUninstalled 70
  HP3720  2014-06-11  ComputerUninstalled 70
  </code></pre>