In building databases and creating queries to grab data from them, I rarely came across a problem which I couldn't solve. Today I
figured out the answer to the problem, so I'm sharing it for anyone who has the same problem. The solution will seem simple, and
perhaps obvious to some, but since I use MySQL 4 at work, my skills with harnessing the power of subqueries is subpar. This of
course, is because subqueries exist only in MySQL 5 and later.
Take the following set of data:
+----------------------+
| LoginLog |
+----------------------+
| User | LoginDate |
+----------------------+
| Brandon | 2009-08-02 |
| Brandon | 2009-08-07 |
| Brandon | 2009-08-09 |
| Mark | 2009-06-03 |
| Mark | 2009-06-30 |
| Jackie | 2009-08-05 |
+----------------------+
With this data, say you want to create a report showing each user who has logged in, along with the last date they logged in on. Like
so:
+----------------------+
| User | LoginDate |
+----------------------+
| Brandon | 2009-08-09 |
| Mark | 2009-06-30 |
| Jackie | 2009-08-05 |
+----------------------+
Your initial thought may be to write this query:
SELECT User, LoginDate FROM LoginLog GROUP BY User ORDER BY LoginDate DESC;
Though, this is likely to produce something like the below, with the wrong dates, which we don't want:
+----------------------+
| User | LoginDate |
+----------------------+
| Jackie | 2009-08-05 |
| Brandon | 2009-08-02 |
| Mark | 2009-06-03 |
+----------------------+
The problem with this is that the GROUP BY happens before the ORDER BY does. The ordering occurs on the set of data the GROUP BY
returns. In this example, we wanted the ordering to happen first, and then the grouping would eliminate all other LoginDates except
the most recent.
The way to solve this problem is through the use of a subquery as one of the selected columns.
SELECT a.User,
(SELECT b.LoginDate
FROM LoginLog b
WHERE a.User = b.User
ORDER BY b.LoginDate DESC
LIMIT 1
) AS LoginDate
FROM LoginLog a
GROUP BY a.User;
This is the same query as before, only we are grabbing the LoginDate using a subquery, which will do the ordering for us. And there
you have it, problem solved.