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.

please specify your question: changed since some date or ever — IVNSTN
– IVNSTN, Commented Feb 14, 2016 at 12:39
SQL SERVER or MYSQL? and look at my answer , should do the trick — sagi
– sagi, Commented Feb 14, 2016 at 12:44
even top 1 with ties with filter by date might satisfy your needs — IVNSTN
– IVNSTN, Commented Feb 14, 2016 at 12:47

sagi · Accepted Answer · 2016-02-14 12:59:10Z

2

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

edited Feb 14, 2016 at 12:59

answered Feb 14, 2016 at 12:35

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kevin Kopf Over a year ago

yeah, but if you run select *, the date will still be 2016-02-13

johnyyy Over a year ago

i also want the change / difference. Look at the bit.ly link above. Any idea ?

johnyyy Over a year ago

I need to show date-wise change. By using Max() and Min() you arent taking account of date. There's no way to tell if change is negative or positive.

Kevin Kopf · Accepted Answer · 2016-02-14 13:12:42Z

1

Based on the ideas given, here's what I've got:

SELECT sitename, rank, rank - (SELECT rank from sites WHERE sitename = main.sitename ORDER BY date LIMIT 1,2) as rankChange
FROM sites as main
WHERE date <= NOW()
GROUP BY sitename
HAVING COUNT(DISTINCT rank) > 1

This will return

sitename    rank    rankChange
aol.com        4             1
yahoo.com      3            -1

If you remove HAVING COUNT(DISTINCT rank) > 1 from the query, you will see the set with the unchanged webs:

sitename    rank    rankChange
aol.com        4             1
facebook.com   1             0
gmail.com      2             0
yahoo.com      3            -1

edited Feb 14, 2016 at 13:12

answered Feb 14, 2016 at 13:05

Kevin Kopf

14.3k15 gold badges57 silver badges69 bronze badges

5 Comments

Kevin Kopf Over a year ago

@johnyyy there's a whole chapter on that in mysql documentation: dev.mysql.com/doc/refman/5.5/en/select-optimization.html Sorry, but I can't generate millions of rows of mock data just to optimize this query for you.

johnyyy Over a year ago

is there any other way using max(date) and min(date) ?

Ivan Over a year ago

@johnyyy I think this is an answer. How to optimize that is another question

johnyyy Over a year ago

@Ivan The query keeps loading, havent seen the result of this query

Ivan Over a year ago

@johnny Have you made restrictions in where clause by date? I suppose you have a start and end date for which you want a change. In example there is no restriction in fact

Gouda Elalfy · Accepted Answer · 2016-02-14 13:08:04Z

0

you can do by like this:

select * from your_table t_outer
group by sitename, date
having rank <> (select rank from your_table t_internal 
where t_outer.date > t_internal.date and t_outer.sitename = t_internal.sitename
order by t_internal.date limit 1)

and it is output:

edited Feb 14, 2016 at 13:08

answered Feb 14, 2016 at 12:53

Gouda Elalfy

7,0833 gold badges28 silver badges38 bronze badges

1 Comment

Gouda Elalfy Over a year ago

@johnyyy it gives me the output in the picture I upload

Ivan · Accepted Answer · 2016-02-14 16:34:23Z

0

Something like following. And you need a record for every site every day

SELECT sitename, MAX(rank) - MIN(rank) as ChangeRank
FROM ChangeHistoryTable
WHERE date between dateFromWhichYouNeedChanges and dateToWhichYouNeedChanges
GROUP BY sitename
HAVING COUNT(DISTINCT rank) > 1

edited Feb 14, 2016 at 16:34

answered Feb 14, 2016 at 12:57

Ivan

3121 gold badge4 silver badges14 bronze badges

2 Comments

Kevin Kopf Over a year ago

the result is both yahoo and aol show -1 (if I use now())

Ivan Over a year ago

@Nordenheim Yes, I missundertstood the task. Then your answer is right

Muhammad Muazzam · Accepted Answer · 2016-02-14 21:37:36Z

0

Try this:

Select sitename from table group by sitename having count(*) > 1 order by sitename, rank DESC

edited Feb 14, 2016 at 21:37

answered Feb 14, 2016 at 12:40

Muhammad Muazzam

2,8006 gold badges37 silver badges64 bronze badges

1 Comment

sagi Over a year ago

Now thats almost a copy of my answer, and still won't work since you are missing the distinct in the count which will results in returning every one that appeared twice or more on the table no matter if the rank changed. Also, order by in unnecessary .

Collectives™ on Stack Overflow

sql find rows with different column values

5 Answers 5

3 Comments

5 Comments

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

5 Comments

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related