title: SQL: selecting multiple rows per group
body: <p>I have some records in my table <code>tbl1</code>. There is a column <code>s</code>, based on which I need to retrieve the rows where <code>datateime1</code> is an old date. </p>
  
  <p>If I try the query shown below I seem to get correct results. Now I try to get the <em>two</em> oldest rows of the <code>s1</code> group. I tried by adding <code>'s1'</code> twice to the IN clause, but that does not work, because the rows are selected by their ROWNUMBER.</p>
  
  <p>So how can I rewrite the query to select a flexible number of rows per group (starting with the two oldest dates)?</p>
  
  <p>Query:</p>
  
  <pre><code>SELECT
    tbl2.* 
  FROM
    (
     SELECT
       a, b, c, 
       ROW_NUMBER() OVER (PARTITION BY tbl1.s ORDER BY tbl1.Datetime1)) AS RowNo 
     FROM
       tbl1 
     WHERE
       tbl1.s in ('s1','s2','s2','s3','s1','s4')
    ) AS tbl2 
  WHERE
    tbl2.rowno = 1
  </code></pre>
  
  <p>tbl1 looks like:</p>
  
  <pre><code>a          b          c          s      Datetime1
  -------------------------------------------------
  samp1a     samp1b     samp1c     s1     1/1/2000
  samp1a     samp1b     samp1c     s1     2/1/2000
  samp1a     samp1b     samp1c     s1     3/1/2000
  samp2a     samp2b     samp2c     s2     2/1/2002
  samp2a     samp2b     samp2c     s2     3/1/2002
  samp3a     samp3b     samp3c     s3     3/1/2000
  </code></pre>
  
  <p>The desired output is:</p>
  
  <pre><code>a          b          c          s      p
  -------------------------------------------------
  samp1a     samp1b     samp1c     s1     1/1/2000
  samp1a     samp1b     samp1c     s1     2/1/2000
  samp2a     samp2b     samp2c     s2     2/1/2002
  samp3a     samp3b     samp3c     s3     3/1/2000
  </code></pre>