Is it possible to sort properties where the owner has been the same for more than three years in a row?

Question

MySQL

Hello guys.
Actually a subject.
Is it possible using sql to get records where the owner was the same for more than three years in a row from the table below? That is, the request sounds something like this - give me a property (property_id) and the name of the owner, where the owner has been the same for three years in a row .

If not, please tell me where to dig? Write a function that will take care of all the work and put the results in another table?
Thank you in advance.

Answer 1

Something like this:

select 
t1.property_reg_num, 
t1.property_owner_name_id 
from property_table t1
inner join
(
 select  
 property_reg_num, 
 property_owner_name_id,
 MIN(property_record_on_year) min_year
 from property_table
 group by property_reg_num, 
 property_owner_name_id
) t2 on t2.property_reg_num = t1.property_reg_num 
        and t2.property_owner_name_id = t1.property_owner_name_id
        and t1.property_record_on_year in (t2.min_year, t2.min_year+1, t2.min_year+2)
group by t1.property_reg_num, 
t1.property_owner_name_id

Answer 2

If we approach the issue from the point of view of business logic, and not sql:
Are there so many cases when a person owned real estate for more than 3 years, but at the same time with interruptions?

select 
   property_id, property_owner_name_id 
from 
   tablename  
group by 
   property_id, property_owner_name_id 
having 
   count(property_record_on_year) >= 3

And then check this sample in any other way.

Answer 3

Mysql is sad with such requests. No window functions or anything else useful.
In order of morning delirium, the solution is head-on:

select property_reg_num, property_owner_name_id from
    tablename y1
    join tablename y2 using(property_reg_num, property_owner_name_id)
    join tablename y3 using(property_reg_num, property_owner_name_id)
where y2.property_record_on_year = y1.property_record_on_year + 1
and y3.property_record_on_year = y2.property_record_on_year + 1

With a unique constraint on property_reg_num & property_owner_name_id & property_record_on_year the result should be correct.

Answer 4

As far as I understand, the use of recursive queries is required, if it doesn’t hurt, you can migrate to postgre there is witch
Another option is to write a recursive function, but it will block selects for each ID.
You can also change the structure of the table and replace the year field with two dates, the date of purchase and the date of sale, then a simple select with the condition 2-1 > 3 will work....

Answer 5

Can

select * from (
select t1.regid, t1.ownerid, t1.recyear,
          IFNULL((select min(tmp.recyear) from table tmp
                        where (tmp.regid = t1.regid) and (tmp.recyear >= t1.recyear)), currentyear) endyear
 from table t1
) where (endyear - recyear) > 3