I need some help in creating query :)

Example:
Table name history:
with values:
home update
1.70 2009-11-12 10:15:03
1.70 2009-11-12 10:14:04
1.70 2009-11-12 10:13:02
1.70 2009-11-12 10:12:06
1.70 2009-11-12 10:11:03
1.70 2009-11-12 10:10:03
1.70 2009-11-12 10:09:03
1.70 2009-11-12 10:08:04
1.70 2009-11-12 10:07:03
1.70 2009-11-12 10:06:03
1.70 2009-11-12 10:05:03
1.70 2009-11-12 10:04:03
1.70 2009-11-12 10:03:03
1.70 2009-11-12 10:02:04
1.70 2009-11-12 10:01:03
1.70 2009-11-12 10:00:06
1.70 2009-11-12 09:59:03
1.70 2009-11-12 09:58:05
1.69 2009-11-12 09:57:03
1.69 2009-11-12 09:56:05
1.69 2009-11-12 09:55:03
1.69 2009-11-12 09:54:03
1.69 2009-11-12 09:53:03
1.72 2009-11-12 09:52:03
1.72 2009-11-12 09:51:03
1.72 2009-11-12 09:50:03
1.72 2009-11-12 09:49:03
1.72 2009-11-12 09:48:05
1.72 2009-11-12 09:47:02
1.68 2009-11-12 09:46:04
1.68 2009-11-12 09:45:03
1.68 2009-11-12 09:44:04
1.68 2009-11-12 09:43:03
1.68 2009-11-12 09:42:05
1.68 2009-11-12 09:41:03
1.68 2009-11-12 09:40:04
1.68 2009-11-12 09:39:03
1.68 2009-11-12 09:38:04
1.68 2009-11-12 09:37:02
1.68 2009-11-12 09:36:03
1.68 2009-11-12 09:35:03
1.68 2009-11-12 09:34:05
1.68 2009-11-12 09:33:02
1.68 2009-11-12 09:32:06
1.68 2009-11-12 09:31:02
1.68 2009-11-12 09:30:03
1.68 2009-11-12 09:29:03
1.68 2009-11-12 09:28:04
1.68 2009-11-12 09:27:02
1.68 2009-11-12 09:26:03
1.68 2009-11-12 09:25:03
1.68 2009-11-12 09:24:03
1.68 2009-11-12 09:23:02
1.68 2009-11-12 09:22:04
1.68 2009-11-12 09:21:03
1.68 2009-11-12 09:20:04
1.68 2009-11-12 09:19:02
1.68 2009-11-12 09:18:04
1.68 2009-11-12 09:17:03
1.68 2009-11-12 09:16:05
1.68 2009-11-12 09:15:03
1.68 2009-11-12 09:14:03
1.68 2009-11-12 09:13:02
1.70 2009-11-12 09:12:05
1.70 2009-11-12 09:11:03
1.70 2009-11-12 09:10:04
1.70 2009-11-12 09:09:03
1.70 2009-11-12 09:08:02
1.70 2009-11-12 09:07:02
1.70 2009-11-12 09:06:03
1.70 2009-11-12 09:05:03
1.70 2009-11-12 09:04:04
1.70 2009-11-12 09:03:03
1.70 2009-11-12 09:02:02
1.70 2009-11-12 09:01:02
1.73 2009-11-12 09:00:03
1.73 2009-11-12 08:59:02
1.73 2009-11-12 08:58:03
1.73 2009-11-12 08:57:03
1.73 2009-11-12 08:56:03
1.73 2009-11-12 08:55:03
1.73 2009-11-12 08:54:03

I would need query that gets me all the rows where value of home has changed compared to previous row.

1.73 2009-11-12 08:54:03
1.70 2009-11-12 09:01:02
1.68 2009-11-12 09:13:02
1.72 2009-11-12 09:47:02
1.69 2009-11-12 09:53:03
1.70 2009-11-12 09:58:05

No PHP looping suggestions :) I would like to know can I get the values using only SQL queries.

1.73 2009-11-12 08:54:03
is this a single column?
or 2 columns, like 1.73 and 2009-11-12 08:54:03 ?

Two columns. Seems that this is impossible to do with just two columns and without PHP looping.

This thread might get you on the right track: http://forums.mysql.com/read.php?108,140154,184714#msg-184714

what you can try to get rows on home value change,
e.g SELECT home, MIN(update) FROM tbl GROUP BY home;

but problem in your case is that home is not unique and above query dismiss new home updates.
solution to your problem needs more thinking :)

When you say "...home has changed compared to previous row", how do you sort your results? Some sort of auto-increment ID? Date? or home column?

Further research yields another solution that might be more applicable to your situation: http://forums.mysql.com/read.php?10,66071,66095#msg-66095

Is the occurrence of home repeating? i.e. 1.70

1.70
1.70
1.63
1.63
1.70
1.70

If not, perhaps SELECT DISTINCT home FROM table ORDER BY update ASC ?

Suppose the data were

1.73 2009-11-12 08:57:03
1.73 2009-11-12 08:56:03
1.73 2009-11-12 08:55:03
1.73 2009-11-12 08:54:03
1.73 2009-11-12 08:57:03
1.73 2009-11-12 08:56:03
1.73 2009-11-12 08:55:03
1.73 2009-11-12 08:54:03

What would you want the output to be?

what you can try to get rows on home value change,
e.g SELECT home, MIN(update) FROM tbl GROUP BY home;

but problem in your case is that home is not unique and above query dismiss new home updates.
solution to your problem needs more thinking :)

Exactly! That is my problem :)

One solution:

Add new column, for example SampleField (INT) and filling it by number with the next conditions when new row adding:
1. if home value is the same as previous do not change SampleField fiels value
2. if home value is changed than increment SampleField value by 1

Then query like:
SELECT SampleField, home, min(oddsupdate) AS oddsupdate FROM YourTable GROUP BY SampleField, home

But this requires one select query for every insert, but propably it is the only possible solution or what do you think :)

But this requires one select query for every insert, but propably it is the only possible solution or what do you think :)

Technically, but not necessarily in two separate satements (assuming variables are already escaped and validated :)):


INSERT INTO history (home, update, changed)
VALUES ($home, '$update',
IF($home = (SELECT home
FROM history
ORDER BY update DESC
LIMIT 1), 0, 1));
So, the subquery grabs the most recent value of home, the IF statement compares that to the value you are inserting and sets changed to 0 if the value is the same, otherwise 1.

To grab all instances of changed data, just "SELECT home, update FROM history WHERE changed"

Further research yields another solution that might be more applicable to your situation: http://forums.mysql.com/read.php?10,66071,66095#msg-66095

Thanks Mattle for the help :)

Lets add auto_id column like in example:

auto_id data timestamp
1 1.50 2009-11-12 17:37:02
2 1.60 2009-11-12 17:37:09
3 1.50 2009-11-12 17:37:16
4 1.50 2009-11-12 17:37:22
5 1.50 2009-11-12 17:45:08
6 1.50 2009-11-12 17:45:23
7 1.55 2009-11-12 17:45:38
8 1.50 2009-11-12 17:47:03


First value is in ID:1

Query:

select auto_id
from(
select T1.auto_id, T1.data data1, T2.data data2
from testi T1
left join (
select *
from testi T
order by auto_id desc
)T2 on T1.auto_id>T2.auto_id
group by T1.auto_id
)T
where T.data1 != T.data2


Gives me the ids:
auto_id Ascending
2
3
7
8

Can I add some extra stuff to that query so it would return me also values in those ID:S , getting little complicated I like challenges :)

Sorry, I failed to give a proper example question.

Suppose the data were

1.73 2009-11-12 08:57:03
1.72 2009-11-12 08:56:03
1.71 2009-11-12 08:55:03
1.70 2009-11-12 08:54:03
1.73 2009-11-12 08:57:03
1.72 2009-11-12 08:56:03
1.71 2009-11-12 08:55:03
1.70 2009-11-12 08:54:03

What would you want the output to be?

Each value is changed compared with the previous row; so do you want all eight rows, even though four of them are duplicates?

Thanks Mattle for the help :)

Lets add auto_id column like in example:

auto_id data timestamp
1 1.50 2009-11-12 17:37:02
2 1.60 2009-11-12 17:37:09
3 1.50 2009-11-12 17:37:16
4 1.50 2009-11-12 17:37:22
5 1.50 2009-11-12 17:45:08
6 1.50 2009-11-12 17:45:23
7 1.55 2009-11-12 17:45:38
8 1.50 2009-11-12 17:47:03


First value is in ID:1

Query:

select auto_id
from(
select T1.auto_id, T1.data data1, T2.data data2
from testi T1
left join (
select *
from testi T
order by auto_id desc
)T2 on T1.auto_id>T2.auto_id
group by T1.auto_id
)T
where T.data1 != T.data2
Gives me the ids:
auto_id Ascending
2
3
7
8

Can I add some extra stuff to that query so it would return me also values in those ID:S , getting little complicated I like challenges :)

Yep...just add the other fields to the field list of the first select statement.

select auto_id, data1, timestamp

and add the timestamp to the fields you're selecting in the subquery

select T1.auto_id, T1.data data1, T2.data data2, T1.timestamp

Yep...just add the other fields to the field list of the first select statement.

select auto_id, data1, timestamp

and add the timestamp to the fields you're selecting in the subquery

select T1.auto_id, T1.data data1, T2.data data2, T1.timestamp

Yep, works now perfectly!