'NOT GREATER THAN' condition Mysql

Question

I have a mysql table with columns userid and createdon where createdon column has DATE datatype.

---------------------------------------------------------------------------------
ID                      UserId                         CreatedOn
1                         65                           2013-06-13 
2                         54                           2013-07-03
3                         34                           2013-08-23
4                         65                           2013-09-13 
5                         89                           2013-09-13

Now I want the userids where last createdon was before 2013-09-08. The correct answer will be userids 54,34

Using

select userid from table  where createdon <'2013-09-08'

returns 65,54,34 and usind

select userid from table where userid notin (select userid from table where createdon > '2013-09-07')

takes a lot of time.

How to get the rows where last createdon < 2013-09-08

Answer 1

Try

SELECT UserID, MAX(CreatedOn) CreatedOn
  FROM table1
 GROUP BY UserId
HAVING CreatedOn < '2013-09-08'

Output:

| USERID |                     CREATEDON |
|--------|-------------------------------|
|     34 | August, 23 2013 00:00:00+0000 |
|     54 |   July, 03 2013 00:00:00+0000 |

Here is SQLFiddle demo