SQL group by problem

Question

I have a query which does a number of joins and has a few criteria in the WHERE clause, and I'm ending up with a result which essentially looks like this:

| userId |       date |  otherData |
|--------+------------+------------|
|      1 | 2008-01-01 |  different |
|      1 | 2009-01-01 |       info |
|      1 | 2010-01-01 |        for |
|      2 | 2008-01-01 |       each |
|      3 | 2008-01-01 |        row |
|      3 | 2009-01-01 |       here |

So, in essence for each user, there will be one or more dates in the past, and 0 or more dates in the future.

I need to somehow reduce the dataset to one row per user, only selecting the row which has the most recently passed date. That is, with whatever magic GROUP BY or HAVING clause is added, the result from above would look like this:

| userId |       date |  otherData |
|--------+------------+------------|
|      1 | 2009-01-01 |       info |
|      2 | 2008-01-01 |       each |
|      3 | 2009-01-01 |       here |

Answer 1

I think you don't want to use GROUP BY / HAVING because you are interested in exactly 1 row per user, and that row already exists in the table as-is. This calls for a WHERE clause and not GROUP BY / HAVING.

My suggestion is that in the WHERE clause, you add a condition that the date must be equal to the result of a subquery. That subquery should:

1. Select the max(date)
2. Have a WHERE clause that limits date to be smaller than NOW
3. Have a WHERE clause with UserID equals outer query's user id

To prevent cases where a certain user can have two entries with the same date that's also the maximum "passed" date, you should also add DISTINCT.

Hope that helps.

Answer 2

Like this:

select a.userId, a.date, b.userId, b.otherData
from table1 as a 
left outer join table2 as b on a.userId = b.userId
where b.Id in (
    select top 1 Id from table2 as c where c.userId = a.userId)

Answer 3

Select 
  userID, date, otherData 
from 
  yourTable t1
where 
  date = 
    (select max(date) from yourTable t2
     where t1.userID=t2.userID)

Answer 4

select T.userid, T.date, T.otherdata 
from 
    T, 
    (select userid, max(date) max_date from T group by userid) GT
where 
    T.userid = GT.userid and T.date = GT.max_date

Explanation: Inner select - izolate only the records with the max date for each user. entire select - join izolated records with the original table, to get the otherdata field.

I assumed that there is only one max date for a user id. Please say if it is the correct assumption.