Alex G.P. Alex G.P. - 4 months ago 7
MySQL Question

How to detect whether specific value contains in MySQL table windows

I have data structure like:

id property_id status tran_id as_of
1 1 sold 1111 2015-04-01
2 1 listed 1111 2015-01-05
3 1 pending 1111 2015-01-02

7 2 listed 2211 2014-09-01
8 2 delisted 2211 2014-06-01
9 2 listed 2211 2014-04-01
10 2 delisted 2211 2014-01-01
11 2 sold 2211 2010-01-01

12 3 sold 6661 2015-08-01
13 3 pending 6661 2015-04-05
14 3 listed 6661 2015-04-01
...


What I want is to detect whether specific property was sold (i.e. last status when
order by as_of asc
is not
sold
). So, for my case property 1 and 3 is sold and 2 isn't. I know how to do it using
OVER()
and
PARTITION BY()
from MS SQL Server, but now I am working with MySQL and I am totally stuck on it (I don't know MySQL as well as MSSQL).

Answer

You can get the last status for each property by doing:

select t.*,
       (case when status = 'sold' then 1 else 0 end) as is_sold
from t
where t.date = (select max(t2.date)
                from t t2
                where t2.property_id = t.property_id
               );

If you have a large amount of data, then an index on t(property_id, date) is recommended.