Query for newest record in a table, store query as view

Question

I'm trying to turn this query into a view:

SELECT t.*
 FROM user t
 JOIN (SELECT t.UserId, 
              MAX( t.creationDate ) 'max_date'
         FROM user t
     GROUP BY t.UserId) x ON x.UserId = t.UserId
                         AND x.max_date = t.creationDate

But views do not accept subqueries.

What this does is look for the latest, newest record of a user. I got the idea from this other stackoverflow question

Is there a way to turn this into a query with joins, perhaps?

Answer 1

Create two views

 Create View MaxCreationDate
 As
      SELECT t.userId, Max(t2.CreationDate) MaxCreated
      FROM user t 
      Group By t.UserId

Create View UserWithMaxDate
As
      Select t.*, m.MaxCreated From user t
         Join MaxCreationDate m
             On m.UserId= t.UserId

and then just call the second one...

EDIT: hey, based on comment from Quassnoi, and your inclusion of where t.CreationDate = MaxDate in yr orig sql, I wonder if you want to see all rows for each distinct user, with the max creation date for that user in every row, or, do you want only one row per user, the one row that was created most recently?

If the latter is the case, as @Quassnoi suggested in comment, change the second view query as follows

Create View UserWithMaxDate
As
      Select t.*, m.MaxCreated From user t
         Join MaxCreationDate m
             On m.UserId= t.UserId
                 And m.MaxCreated = t.Creationdate

Answer 2

CREATE INDEX ix_user_userid_creationdate_id ON user (userid, creationdate, id);

CREATE VIEW v_duser AS
SELECT  DISTINCT userId
FROM    user;

CREATE VIEW v_lastuser AS
SELECT  u.*
FROM    v_duser ud
JOIN    user u
ON      u.id =
        (
        SELECT  ui.id
        FROM    user ui
        WHERE   ui.userid = ud.userid
        ORDER BY
                ui.userid DESC, ui.creationdate DESC, ui.id DESC
        LIMIT 1
        );

This is fast and deals with possible duplicates on (userid, creationdate).