title: SQL Order and group by
body: <p>I'm getting a bit lost in making a query that performs a certain look up.
  I have the first part of the query going, which returns me all accounts that have missing some entries on their account. Now I need to filter this subset further based on their last login attempt.</p>
  
  <p>The table structures are as follows:</p>
  
  <ul>
  <li>The users table contains all user information. We only care about users under project_id 33.</li>
  <li>The users_account_list contains all accounts a user has. We only care about users NOT having an entry for service 50.</li>
  <li>The users_login_logs contains all login attempts for a user.</li>
  </ul>
  
  <p>The original query I have this this:</p>
  
  <pre><code>SELECT u.id,
         u.login, 
         u.email, 
         u.nickname,
         b.station_login AS "additionals.station_login",
         a.id AS "user_account_list.id",
         a.game_id AS "user_account_list.game_id",
         a.game_uid AS "user_account_list.game_uid",
         c.created_at AS "last login"
  FROM users u
  LEFT JOIN user_account_list a ON u.id = a.user_id AND a.game_id = 50
  LEFT JOIN user_additionals b ON u.id = b.id
  LEFT JOIN user_login_logs c ON u.id = c.user_id
  WHERE u.project_id = 33
  AND u.verified_at IS NOT NULL
  AND (a.id IS NULL OR a.game_id IS NULL OR a.game_uid IS NULL)
  AND (b.station_login IS NULL OR b.station_login = '')
  ORDER BY c.created_at DESC
  </code></pre>
  
  <p>This returns me all users that have been registered under project_id 33, and do not have an entry for game_id 50, and have no information stored in their additional info table. Optional, but not relevant, Just limits the data returned. It does give me multiple rows per user back , sorted according their latest login date.</p>
  
  <p>What I need is to get only 1 row per user returned with their LATEST login date. <em>I tried replacing the ORDER BY with GROUP by u.id</em> but this gives me the oldest result back, not the latest.</p>
  
  <p>How can I:</p>
  
  <ul>
  <li>Limit the rows returned to only 1 row per user</li>
  <li>Make sure the row is based on the latest login attempt of the user.</li>
  </ul>
  
  <p><em>EDIT</em>:</p>
  
  <p>This is what the query currently returns:</p>
  
  <pre><code>+----+-------+-----------------+----------+---------------------------+----------------------+---------------------------+----------------------------+---------------------+
  | id | login | email           | nickname | additionals.station_login | user_account_list.id | user_account_list.game_id | user_account_list.game_uid | last login          |
  +----+-------+-----------------+----------+---------------------------+----------------------+---------------------------+----------------------------+---------------------+
  |  1 | usrnm | someon@mail.com | Nickname |                           |                 NULL |                      NULL | NULL                       | 2012-10-19 00:00:00 |
  |  1 | usrnm | someon@mail.com | Nickname |                           |                 NULL |                      NULL | NULL                       | 2012-10-18 00:00:00 |
  |  1 | usrnm | someon@mail.com | Nickname |                           |                 NULL |                      NULL | NULL                       | 2012-10-17 00:00:00 |
  +----+-------+-----------------+----------+---------------------------+----------------------+---------------------------+----------------------------+---------------------+
  3 rows in set (0.08 sec)
  </code></pre>