CODEWITHSUNDEEP
mysql
Rijvi Rajib
Rijvi Rajib

Reputation: 1080

Compare first and last SQL row with same id

Here's my SQL Fiddle: http://sqlfiddle.com/#!2/90d45/3

Table scheme:

CREATE TABLE domain_rankings (domain_id int, rank int, create_date datetime);
INSERT INTO domain_rankings VALUES (1, 0, "2012-01-01");
INSERT INTO domain_rankings VALUES (1, 2, "2012-01-02");
INSERT INTO domain_rankings VALUES (1, 1, "2012-01-03");
INSERT INTO domain_rankings VALUES (2, 0, "2012-01-01");
INSERT INTO domain_rankings VALUES (2, 1, "2012-01-02");
INSERT INTO domain_rankings VALUES (2, 2, "2012-01-03");
INSERT INTO domain_rankings VALUES (3, 1, "2012-01-01");
INSERT INTO domain_rankings VALUES (4, 3, "2012-01-01");
INSERT INTO domain_rankings VALUES (4, 2, "2012-01-02");
INSERT INTO domain_rankings VALUES (4, 1, "2012-01-03");

I want to get the count of the domain_id's that go up in rank comparing the first entry to the last entry (by date).

So in this case, for the count of all ranks going up should be 2 (domain_id: 1, 2).

domain_id 3 should not be included in the count because it has only one entry. So, probably need a subquery of HAVING COUNT(*) > 1.

domain_id 4 also shouldn't be included in the count unless I reverse the query and want the ranks that go down.

How should I go about solving this problem? I'm aware that I'll need subqueries, but I have no idea where to begin with it. The JSFiddle should give you an idea of where I'm stuck.

Attempts - this returns the rows that are valid, but not the COUNT of the rows correctly: 

SELECT domain_id, COUNT(DR.domain_id)
FROM domain_rankings DR
WHERE
  (SELECT rank
   FROM domain_rankings
   WHERE domain_rankings.domain_id = DR.domain_id
   ORDER BY create_date ASC LIMIT 1
  ) > (
    SELECT rank
    FROM domain_rankings
    WHERE domain_rankings.domain_id = DR.domain_id
    ORDER BY create_date DESC LIMIT 1
  )
GROUP BY DR.domain_id
HAVING count(*) > 1

Final answer (slower of the two options):

SELECT COUNT(a.domain_id) cnt
FROM (
  SELECT domain_id,MIN(create_date) mind, MAX(create_date) maxd
  FROM domain_rankings
  GROUP BY domain_id
  HAVING COUNT(*) > 1
) master
JOIN  domain_rankings a 
  ON a.domain_id = master.domain_id 
 AND a.create_date = master.mind
JOIN  domain_rankings b
  ON b.domain_id = master.domain_id 
 AND b.create_date = master.maxd
WHERE a.rank < b.rank

Upvotes: 1

Views: 1266

Answers (2)

AdrianBR
AdrianBR

Reputation: 2588

SELECT count(*)
FROM TABLE AS a
INNER JOIN TABLE AS b ON a.domain_id=b.domain_id
AND a.date=b.date-interval 2 DAY
AND a.rank<b.rank

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181027

Maybe not the simplest approach, but you can for example use a subquery to get the min and max date per domain_id, and use regular joins to get the corresponding lines. Then you can just compare the ranks at the end;

SELECT COUNT(a.domain_id) cnt
FROM ( SELECT domain_id,MIN(create_date) mind, MAX(create_date) maxd
       FROM domain_rankings GROUP BY domain_id) master
JOIN  domain_rankings a 
  ON a.domain_id = master.domain_id AND a.create_date = master.mind
JOIN  domain_rankings b
  ON b.domain_id = master.domain_id AND b.create_date = master.maxd
WHERE a.rank < b.rank

Another version is to just use LEFT JOINS to filter out the first and last row, and compare the ranks;

SELECT COUNT(a.domain_id) cnt
FROM domain_rankings a JOIN domain_rankings b
  ON a.domain_id = b.domain_id AND a.rank < b.rank
LEFT JOIN domain_rankings c
  ON a.domain_id = c.domain_id AND a.create_date > c.create_date
LEFT JOIN domain_rankings d
  ON b.domain_id = d.domain_id AND b.create_date < d.create_date
WHERE c.domain_id IS NULL and d.domain_id IS NULL;

An SQLfiddle with both.

Upvotes: 1

Related Questions