title: MySql : Create view JOIN get max and min value from child table
body: <p>Say, I have table named 'member'</p>
  
  <pre><code>    id     name
   ===================
      a      John
      b      Frank
  </code></pre>
  
  <p>Member have 'login_activities'</p>
  
  <pre><code>    id     login_time     gol     member
  ==========================================
      1      2012-10-01      99      a
      2      2012-10-01     125      b
      3      2012-11-01     255      a
      4      2012-11-02     111      b
      5      2012-11-07     101      a
  </code></pre>
  
  <p>I want to create view that have information about when first time login and last time login of the members and also the 'gol' value only from the last login.</p>
  
  <p>Is it possible?</p>
  
  <p>I've tried many ways. One of my attempt:</p>
  
  <pre><code>CREATE VIEW   view_firstlast
       AS
            SELECT
                 MIN(a.login_time) as first_login,
                 MAX(a.login_time) as last_login,
                 a.gol,
                 b.name
            FROM
                 login_activities a
                     JOIN member b ON a.member = b.id
            GROUP BY
                 a.member
  </code></pre>
  
  <p>It does'nt get right result. The 'gol' value is from the first login.
  How to do this?</p>
  
  <hr>
  
  <p>Something important here: <strong>MySQL doesn't allow subquery in create view</strong></p>