Question 20706 in Mysql Question posted in
The official MySQL documentation can be found here.

mySQL – Find Trees in DB that have been Dead for 5 years?

I’m wondering if there is a more concise way of finding trees that have had a Vigor rating of 6, 8 or 9 for the past 5 years.

In the past, I was looking trees dead only for the past two years, and I used a query like this:

set @sampleyear = 2020;
select *
from tblTree
where tblTree.Year=@sampleyear
and (((Year = @sampleyear) and (Vigor not in (6,8,9))) or ((Year = (@sampleyear-1)) and (Vigor not in (6,8,9))));

I could expand the last line to just continue back for 5 years, but I was wondering if anyone could think of a better way?

Thank you!

Sample data could look like this, the query would return rows with year 2020 and tree id 1 but not 2:

tree id year vigor
1 2015 1
1 2016 1
1 2017 1
1 2018 6
1 2019 6
1 2020 6
2 2015 6
2 2016 6
2 2017 6
2 2018 6
2 2019 6
2 2020 6
Tags:

2

Answers


  1. 0

    You can do this by comparing YEAR and NOW subtracting an arbitrary interval set by you. IE

    SELECT * FROM table WHERE YEAR(NOW() - interval 7 year) > `year`;

    A side note, you should not name your field year in your table. This is a reserved function in MySQL. You can "get around" it by putting it in ticks

    `year`

    Which using ticks is always best practice, but it’s even better practice to use the ticks AND not use reserved functions as names for your fields.

    Login or Signup to reply.
  2. 0

    Your existing query does not make much sense. Here it is with @sampleyear expanded:

    select *
from tblTree
where tblTree.Year = 2020
and (
    (Year = 2020 and Vigor not in (6,8,9)) or
    (Year = 2019 and Vigor not in (6,8,9)) -- this can never be true as you have tblTree.Year = 2020 outside this group
);

    Which is the same as just:

    select *
from tblTree
where tblTree.Year = 2020
and Vigor not in (6,8,9);

    This is the basic extension of your existing query, just using >= comparison for the year instead of listing them explicitly and grouping so as to return just the list of tree_ids:

    /* Any years >= 2018 Vigor NOT IN (6, 8, 9) */
SELECT `tree_id`
FROM `tblTree`
WHERE `year` >= YEAR(NOW()) - 5
AND `vigor` NOT IN (6, 8, 9)
GROUP BY `tree_id`;

    These are variations on this theme, which you may find useful:

    /* All years >= 2018 Vigor IN (6, 8, 9) */
SELECT `tree_id`
FROM `tblTree`
WHERE `year` >= YEAR(NOW()) - 5
GROUP BY `tree_id`
HAVING MIN(`vigor` IN (6, 8, 9)) = 1;

/* All years >= 2018 Vigor NOT IN (6, 8, 9) */
SELECT `tree_id`
FROM `tblTree`
WHERE `year` >= YEAR(NOW()) - 5
GROUP BY `tree_id`
HAVING MIN(`vigor` NOT IN (6, 8, 9)) = 1;

/* Any years >= 2018 Vigor IN (6, 8, 9) */
SELECT `tree_id`
FROM `tblTree`
WHERE `year` >= YEAR(NOW()) - 5
AND `vigor` IN (6, 8, 9)
GROUP BY `tree_id`;

    Any of these queries can be easily joined back to the full table if you want to see the full records for the time span being looked at:

    SELECT `tblTree`.*
FROM (

    /* query from above */
    /* all years >= 2018 Vigor IN (6, 8, 9) */
    SELECT `tree_id`
    FROM `tblTree`
    WHERE `year` >= YEAR(NOW()) - 5
    GROUP BY `tree_id`
    HAVING MIN(`vigor` IN (6, 8, 9)) = 1
    /* end query from above */

) t
JOIN `tblTree`
    ON `t`.`tree_id` = `tblTree`.`tree_id`
    AND `tblTree`.`year` >= YEAR(NOW()) - 5
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search