MySQL Query Nightmare with RETs data

Question

For those of you who have actually delt with RETS may be able to give me a hand here. The problem occurs when multiple properties are tied into the RETS data even though the property is sold. Basically what I need is to be able to check the database with the SELECT statement against three fields. The fields in question would be C_StreetName, C_StreetNumber, and C_PostalCode.

To make this clear what I want is some type of way to check for duplicates while gathering the dataset, this can't be done in php because of how the data is returned through the application. So if it finds another record with the same C_StreetName, C_StreetNumber, and C_PostalCode it will remove them from the dataset. Ideally it would be nice if it could also check the Status of the two to find out if one is Expired or Sold before removing them from the data.

I'm not familiar with complex SQL functions, I was looking at the IF statement until I found that can only be used while storing data not the other way around. And the CASE statement but it just doesn't seem like that would work.

If you guys have any suggestions on what I should use I'd appreciate it. Hopefully there is a way to do this and keep in mind this is only one table I am accessing I don't have any Joins.

Thanks in advance.

Answer 1

Here's something to get you going in the right direction. I haven't tested this, and am not sure you can nest a case expression inside max() in mysql.

What this accomplishes is to output one row per unique combination of street name, number and postcode, with a status of 'Expired' or 'Sold' taking precedence over other values. That is, if there's a row with 'Expired' it will be output in preference to non-expired and non-sold, and a row with 'Sold' will be output if it exists, regardless of what other rows exist for that property. The case statement just converts the status codes into something orderable.

select 
    C_StreetName, 
    C_StreetNumber, 
    C_PostalCode,
    max(
        case status
            when 'Expired' then 1 
            when 'Sold'    then 2
            else 0 
        end) as status
    group by 
        C_StreetName, 
        C_StreetNumber, 
        C_PostalCode;