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.