title: Simulating row_number() MySQL
body: <p>Can you help me with the <code>row_number() over partition</code> in MySql?</p>
  
  <p>I've tried the script below and it doesn't work properly. I'm trying to select only the first product bought. The columns I am selecting are Client_id, product and purchase_date.</p>
  
  <pre><code>select * from(select * from (SELECT @rownum:=@rownum + 1 as row_number,t.* 
  FROM (select cliend_id,prod,purchase_date  from Mytable    
  group by cliend_id,prod,purchase_date order by cliend_id,purchase_Date) asc) t,
  (SELECT @rownum := 0) r)a)b;
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>Row_number  Client ID   Prod    Purchase date
  1               1111    apple   11-Nov-10
  2               1111    yougurt 11-Nov-11
  3               1111    candy   11-Nov-13
  4               2222    chocolate   11-Nov-09
  5               2222    pear    9-Sep-09
  6               2222    beer    12-Sep-10
  7               2222    cheese  12-Sep-14
  8               1234    apple   15-Nov-12
  9               1234    candy   4-Oct-14
  </code></pre>
  
  <p>How can I get this result?</p>
  
  <pre><code>Row_number  Client ID   Prod    Purchase date
  1               1111    apple   11-Nov-10
  2               1111    yougurt 11-Nov-11
  3               1111    candy   11-Nov-13
  1               2222    chocolate   11-Nov-09
  2               2222    pear    9-Sep-09
  3               2222    beer    12-Sep-10
  4               2222    cheese  12-Sep-14
  1               1234    apple   15-Nov-12
  2               1234    candy   4-Oct-14
  </code></pre>
  
  <p>Thanks,
  Rodica</p>