Difference between each row

Databases
1

So say I have data:

Row, Value
01 100
02 200
03 300
04 550
05 600
06 690
07 800

etc…

Is there a way to automate this sort of thing in SQL:

difference between rows 01 and 02 = 100
difference between rows 02 and 03 = 100
difference between rows 03 and 04 = 150
difference between rows 04 and 05 = 50
difference between rows 05 and 06 = 90
difference between rows 06 and 07 = 110

etc…

And end up with a set of data points like:
100
100
150
50
90
110

or would this be better done in PHP?

2

In PHP it’s easy. In MySQL I don’t even know if it’s possible.

3

yeah, you can do it with mysql

where do those “row numbers” come from? how reliably consecutive are they?

it’s important to determine whether there could be any gaps in them

4

The consecutive “row numbers” would actually be the result of a query which creates that sequence. No actual table exists with that sequence.

5

So what table does exist? And what data does it contain? It’s hard to write a query without knowing what to do, and what to do it with.

6

In the interest of simplicity, let’s just say this is the exact table:


CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `field1` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `table1` (`id`, `field1`) VALUES
	(1, 100),
	(2, 200),
	(3, 300),
	(4, 550),
	(5, 600),
	(6, 690),
	(7, 800);

I want a query which will create this result set:

100
100
250
50
90
110

7

in the interest of simplicity, you gave data without any gaps in the numbers

(remember me asking “how reliably consecutive” the numbers are?)

SELECT r2.field1 - r1.field1
  FROM table1 AS r1
INNER
  JOIN table1 AS r2
    ON r2.id = r1.id + 1

vwalah, exact results as requested

8

Exactly.

Yes.

Thanks!

Now I get to figure out how it works. Specifically, what are r1 and r2? What is the r1.field1 construct? Thanks again.

9

r1 and r2 are aliasses. They are needed because you’re joining a table to itself, and mysql needs to know where the columns come from you use in the query. Without them, you’d get an error saying that the column name is specified multiple times.
So the r1.field1 construct indicates the column field1 from the table table1 that you’ve given the alias r1.

10

Great, thanks!