title: Efficient way to get last 10 rows of each group in SQLite
body: <p>Consider there is a table of job runs history with the following schema:</p>
  
  <pre><code>job_runs
  (
      run_id integer not null, -- identifier of the run
      job_id integer not null, -- identifier of the job
      run_number integer not null, -- job run number, run numbers increment for each job
      status text not null, -- status of the run (running, completed, killed, ...)
      primary key (run_id)
      -- ...
  )
  </code></pre>
  
  <p>and it is required to get the last 10 runs with <code>status != 'running'</code> for each job (jobs differ by <code>job_id</code>). To do that I wrote the following query:</p>
  
  <pre><code>SELECT
      *
  FROM
      job_runs AS JR1
  WHERE
      JR1.run_number IN
      (
          SELECT
              JR2.run_number
          FROM
              job_runs AS JR2
          WHERE
              JR2.job_id = JR1.job_id
              AND
              JR2.status != 'running'
          ORDER BY
              JR2.run_number
          DESC
          LIMIT
              10
      )
  </code></pre>
  
  <p>It do what I need, but even though there is a multifield index on the <code>job_id</code> and <code>run_num</code> fields of the <code>job_runs</code> table the query is slow, because it scans job_runs table and for each its row runs subquery. The index helps subqueries to run fast each time, but the fact that the nester query scans entire table kills performance. So how can I tune performance of the query?</p>
  
  <p><strong>some thoughts:</strong></p>
  
  <p>Number of jobs (different <code>job_id</code>s) is small and if there were a FOR loop in SQLite it would be easy to loop over all distinct <code>job_id</code>s and run the subquery
  passing the job id instead of <code>JR1.job_id</code> then UNION all results.</p>
  
  <p><strong>important:</strong></p>
  
  <p>Please don't suggest to run the loop inside the source code of my application. I need pure SQL solution.</p>