Query records that return the latest stored month that doesn't match or excced a passed value-CodePudding

username, month, value, ...(other columns)
--------------------------
brad,     2,     d34
fred,     2,     x12
bob,      2,     y45
brad,     3,     d56
fred,     3,     x67
brad,     4,     d56
brad,     4,     d34
fred,     6,     x78
brad,     8,     d89

Primary Key are the username, month and value

Right now if I do:

SELECT username, value FROM table WHERE name = 'brad' and month = 4

Returned is d56 & d34 which is correct.

but the query needs to be able to handle passing a month 5 or 6 or 7 and still return the last stored value that doesn't exceed the month. i.e. passing 6 returns the value for month 4 still.

CodePudding user response：

Use Limit, Less than or equal to for month, and an order by

SELECT username, `value` 
FROM Table 
WHERE name = 'brad' 
  and `month` <= 6
ORDER BY `month` DESC
LIMIT 1

Note: this assumes you can't have a duplicate month for a name. I'd assume you'd need a year and order by year desc, month desc otherwise.

also note, you'll need to escape the month and value column names as they are reserved words.

***Given the complete change to the nature of the question: **

Some new options are:

We again use the same process as above but now since we can have multiple repeated months for the same user, we need to just get 1 instance of a month and then join back to a base set. We can do that using exists.

SELECT username, `value` 
FROM Table x 
WHERE Exists (
    SELECT username, `month`
    FROM Table y
    WHERE name = 'brad' 
      and `month` <= 6
      and y.username = x.username
      and y.`month` = x.`month`
    ORDER BY `month` DESC
    LIMIT 1)

An alternative approach would be to use a window/analytic function Row_number but I'm unsure of your mySQL version and if you have access to that feature.

  SELECT username, `value` 
  FROM (SELECT username, `month`, 
        Row_Number() Over (PARTITION by userName, `month` ORDER BY `month` DESC) rn
    FROM Table y
    WHERE name = 'brad' 
      and `month` <= 6) x
  WHERE rn = 1

All of this is untested: the last option is the more modern approach; the 2nd to last option works in prior version of mySQL before analytic functions were available. Performance wise, the modern approach should be faster; but testing would have to be done to prove it out and performance should be similar in nature. Indexes would matter on the results.