Disclaimer: While the basis of my question—the greatest-n-per-group question—has been asked and also answered so many times before, I am facing a seemingly hard issue due to extra conditions that I have to apply.
In case I really missed the answer to my exact question that was given somewhere, I am sorry. I searched, read, tried, a lot, and I spent already half my day on this.
I tried to describe the abstract problem in the title: for any other field A value, get the row with some special field B value, and at the same time, respect further conditions.
Now, my concrete problem is as follows... I have the following table:
CREATE TABLE IF NOT EXISTS `mdl_datasets` ( `id` bigint(10) NOT NULL AUTO_INCREMENT, `cmid` bigint(10) NOT NULL DEFAULT '0', `userid` bigint(10) NOT NULL DEFAULT '0', `timecreated` bigint(10) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
For each user (i.e.,
userid) in one specific module (i.e.,
cmid), I want to get the most recent row (i.e., maximum
timecreated), before a given timestamp (i.e., again
timecreated). The latter part makes it hard.
I don't see how I can do this without using at least three (sub)queries, and even then I'm unable to make it work properly in any case.
So far, I only managed to get it partially working, for example, only for users with at least two rows. This is wrong, of course. If there is a user with only a single row, which has a correct
timecreated value, then I want to get this row as a result. For any other user, I want to get the one row with the greatest
timecreated value that is below the given maximum date (as Unix epoch timestamp).
Is there anything else that I should mention? Tell me.