Mysql – How to query group for increase or decrease in value

group byMySQLPHP

In MySQl I have a table positions and I would like to find out if position of certain app for certain feed in a certain country type has been increasing/decreasing and by how much from the last reading.

This is the table structure:

CREATE TABLE `positions` (
  `application_id` int(11) unsigned NOT NULL,
  `country` varchar(2) NOT NULL,
  `feed_id` int(2) NOT NULL,
  `position` int(4) NOT NULL,
  `created` datetime NOT NULL,
  KEY `application_id` (`application_id`,`store`,`feed_id`,`position`),
  KEY `created` (`created`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

country is a two character ISO country code, every row belongs to one application through application_id and feed_id is another table that is helping to sort out the large amount of data.

What the query should look like? I have gotten to a pont where I group by the country code where application and feed id's are in WHERE clause but can't figure out the values 🙁

Best Answer

Join positions to itself. In pseudo-sql:

select
    o.position,
    n.position
from positions as o        -- old
inner join positions as n  -- new
    on o.application_id = n.application_id
    and o.country = n.country
    and o.feed_id = n.feed_id
    and o.created = <max value for this app, country and feed that's less than n.created>

There's a discussion on this SO question for other options.

Related Question