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

Comparing values from table of duplicates – Mysql

I have a query which produces a table of duplicate records from an larger dataset, and I want to be able to do further analysis on this output data.

Below is an example of what would be produced to work from.

I want to be able to identify, for the individual properties, instances where an oldest rating for that property was less than 50, but the newest rating is now greater than 70.

On the example below, only the ratings related to property 17 would meet this criteria, and so the row with Reference jygh would be extracted.

REFERENCE PROPERTYADDRESS DATE RATING
adef PROPERTY12 2022-12-08 70
pout PROPERTY12 2022-12-16 90
mhef PROPERTY17 2023-01-02 25
jygh PROPERTY17 2023-01-09 70
boyt PROPERTY22 2022-10-05 85
qepl PROPERTY22 2022-10-25 28

This is specifically analysis of a change over time so just a max/min comparison of Rating would not be sufficient.

EDIT: I’ve edited the data example to show a drop in Rating value which would not be meet the criteria.

Tags:

2

Answers


  1. 0

    This one is working only if not considering the dates :

    select PROPERTYADDRESS
from TABLE1
where RATING <= 50
and PROPERTYADDRESS in (
 select PROPERTYADDRESS
 from TABLE1
 where RATING >= 70
);

    check it here : https://dbfiddle.uk/6yoRNP74

    Login or Signup to reply.
  2. 0

    This will take in consideration the oldest and the newest rates :

    SELECT mt.PROPERTYADDRESS    
FROM TABLE1 mt 
INNER JOIN
    (
        SELECT PROPERTYADDRESS, MIN(Date) AS MinDate
        FROM TABLE1
        GROUP BY PROPERTYADDRESS
    ) t ON mt.PROPERTYADDRESS = t.PROPERTYADDRESS AND mt.DATE = t.MinDate
 WHERE RATING <= 50
 AND mt.PROPERTYADDRESS in (
     SELECT mt.PROPERTYADDRESS 
     FROM TABLE1 mt 
     INNER JOIN
        (
            SELECT PROPERTYADDRESS, MAX(Date) AS MaxDate
            FROM TABLE1
            GROUP BY PROPERTYADDRESS
        ) t ON mt.PROPERTYADDRESS = t.PROPERTYADDRESS AND mt.DATE = t.MaxDate
     WHERE RATING >= 70
);

    Check it here : https://dbfiddle.uk/XzEIlvKc

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search