I have a view with

downs_history_2022.`Asset Id`,
downs_history_2022.`User Id`,
downs_history_2022.`Log Date`,

INNER JOIN ldapfull_kf ON downs_history_2022.`User Id` = ldapfull_kf.uid

Month is a single or two digit field 1-12. I save everything in a historical table and would like to extract the "last month's" data into a view. Meaning if I last extracted June into the historical data I would like the view to be month = 6... but I don't want to have to go change this every month. (I know I could just create a table with month and year and change those fields in that one table to join but was wondering if I could skip that)

  • Provide: precise MySQL version (output for SELECT VERSION();), sample data (textual code-formatted CREATE TABLE + INSERT INTO) and desired view output for this data.
    – Akina
    Jul 13, 2022 at 4:34
  • How many rows does the view return? Please provide SHOW CREATE VIEW ... Where is the WHERE clause that specifies the year and month? On the SELECT that calls the view? Then let's see that.
    – Rick James
    Jul 14, 2022 at 19:17

1 Answer 1


First of all, a VIEW is just a shorthand for a SELECT. If you can write a SELECT, you can create a VIEW.

Plan A: Save the "6" in a separate, one-line< table.

Plan B: Write a SELECT (perhaps as a subquery in the VIEW you are hypothecating). It would do something SELECT MAX(month) FROM ....

You will probably find that it is messy to have Year and Month in separate columns. (But that is another discussion.)

  • I tried that and only got one row - obviously doing it incorrectly. I added more context in my question. Appreciate your help as always. Also month and year have been surprisingly nice to have in different columns. From a front end perspective we can let users choose month and year separate and fast... but maybe that is because I am a lazy gui programmer!
    – LOSTinDB
    Jul 14, 2022 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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