sql find rows with different column values

Question

My database table is (it has millions of records)

    sitename        rank     date
    facebook.com    1       2016-2-13
    gmail.com       2       2016-2-13
    yahoo.com       3       2016-2-13
    aol.com         4       2016-2-13
    facebook.com    1       2016-2-14
    gmail.com       2       2016-2-14
    yahoo.com       4       2016-2-14
    aol.com         3       2016-2-14

I want to find sites whose ranking has changed. in the above illustration yahoo and aol has changed. I tried several queries but cant get it to work.

Answer 1

Its a simple select, group by and having query like this:

SELECT sitename,MAX(rank) - MIN(rank) as changed
FROM YourTable
GROUP BY sitename
HAVING COUNT(DISTINCT rank) > 1