1

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.

2

I think that is exactly the same case of the max-per-group but with some extended conditions and can be solved by single subquery:

SELECT IF( b.id IS NULL, NULL, a.id ) AS id -- an opposite to COALESCE()
     , a.cmid
     , a.userid
     , b.maxts -- can be NULL 
  FROM table AS a 
  LEFT JOIN ( SELECT id -- if you need no rows for users have no rows before 
                        -- the given timestamp replace the LEFT JOIN 
                        -- by plain JOIN
                   , cmid
                   , userid
                   , MAX(timecreated) AS maxts
                FROM table 
               WHERE cmid = given_cmid
                 AND timecreated < given_timecreated -- additional condition
               GROUP BY userid
            ) AS b ON b.userid = a.userid
                  AND b.cmid   = a.cmid
                  AND b.maxts  = a.timecreated
  WHERE a.cmid = given_cmid
;

This query produce the result for each userid in the table including those having no rows before the given_timecreated but with maxts, id and cmid set to the NULL.

Here condition WHERE cmid = given_cmid is used twice to reduce the size of the subquery result.

Sure you need an index (cmid, userid, timecreated) for acceptable performance. The order columns listed in the index can be different according to the specific data you have.

  • Thank you! That looks ... exotic. :) Right now, I get an error for the IFNULL because of too many parameters...? I am using MySQL (5.6). This function seems to only have two arguments? – tfrommen Oct 1 '18 at 20:52
  • 1
    @tfrommen Yes I mean IF(c,y,n) function – Kondybas Oct 1 '18 at 20:55
  • Thank you! I think this is working nicely. I'm looking into index optimization with EXPLAIN. Oh, and I also have to see if I'm able to make Moodle understand/do this. :) – tfrommen Oct 1 '18 at 21:07
  • @tfrommen As of my experience with Moodle it's too risky to change any part of its data by hand. It can be lookin quite successful until some day. – Kondybas Oct 1 '18 at 21:13
0

Try that:

select user_id,   max(timecreated) from mdl_datasets 
where  cmid=yourmoduleid and timecreated < yourtime
group by user_id ; 

Max will return the very last row before "yourtime", for each user. This works for postgresql, but i am not very sure for mysql, because group by of mysql sometimes actcs like distinct on (column) in postgresql that will bring any row without any criteria for the other columns (because that postgresql, will reject group by with columns out of it if you dont specify the criteria (like max or something)) - but i think because of max mysql will understand the exactly row we want for each user as postgresql does.

  • Thanks for the answer. I'm afraid this doesn't work, though, because I need the whole row, not just the user ID (which is the criteria for the GROUP BY) and the time. When I add id to the fields, I don't get the correct ID, but (like MySQL does), an arbitrary one. Ideas? – tfrommen Oct 1 '18 at 20:28
  • 1
    And if you run exactly the query i answered you get the right id? so then you can use it on a subquery? – Luciano Andress Martini Oct 1 '18 at 20:29
  • Are yout sayng that the query in my answers is getting any id without the respect for max? – Luciano Andress Martini Oct 1 '18 at 20:30
  • Hm, yes, but I mean the ID for the row (i.e., id, not userid). I have the query like so SELECT id, userid, MAX(timecreated) FROM mdl_datasets .... Mind the id that I added. And the row that I get does not have the correct id (for the row with the maximum timecreated) but some other one (in my case the latest/highest). – tfrommen Oct 1 '18 at 20:37
  • Here is a Fiddle: sqlfiddle.com/#!9/c1c90c/3 With the time provided, it works (but just because of the data that I put in there, so it's by coincidence). If you run the query without the timestamp (like it is now, with the commented out code), the result for userid=3 is id=23, which is wrong. It should be id=26, as this is the row with the higher timecreated value. – tfrommen Oct 1 '18 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.