Do these logs live in a database or in a raw file somehwere?

in a MySQL database!

So what I have here is not exactly what you want but it is close. For what I wrote you would need an arg for "word". What this statement will do is return today and the day before today in 2 rows. You can then look at the dif or program something that will do the evaluation for you
****************************

select
..word,
..googleposition,
..yahooposition,
..date
from
..table
where
..date = $today
..and word = 'keyword'

union all

select
..word,
..googleposition,
..yahooposition,
..max(date)
from
..table
where
..date!= $today
..and word = 'keyword'
group by
..date

[edited by: Demaestro at 4:01 pm (utc) on Aug. 25, 2006]

Demaestro - I think there is a problem with the second part of the union - it appears that it will return more than a single row.

If you are going to go this route, a simpler approach might be something like:

SELECT googlepos,yahoopos FROM log WHERE word='concrete' and mydate<=DATEPOINT ORDER BY mydate DESC LIMIT 2

Which would return the 2 rows relating to DATEPOINT, which can then be examined in code.

FD I guess mine assumes that there is only 1 entry per keyword.... per day... then it shouldn't return mutiple rows for the second query, yours does look cleaner though.

Orion, let us know if you need more help. I am sure someone here will help you get around it all.

Yeah, it's one of those things that is relatively easy to solve using a simple bit of SQL and some code, but rather more difficult using pure SQL.
I think if it is really absolutely necessary to use pure SQL (is it for a report field, or something?), then the following will work, but it's not pretty.

SELECT 'googleposition', L.googlepos, CASE WHEN L.googlepos>L2.googlepos THEN 'up' ELSE CASE WHEN L.googlepos<L2.googlepos THEN 'down' ELSE 'nochange' END END FROM log L JOIN log L2 ON L.word=L2.word WHERE L.word='concrete' AND L2.word='concrete' AND L.mydate=DATEPOINT AND L2.mydate=(SELECT MAX(mydate) FROM log L3 WHERE word='concrete' AND mydate<DATEPOINT)
UNION
SELECT 'yahooposition', L.yahoopos, CASE WHEN L.yahoopos>L2.yahoopos THEN 'up' ELSE CASE WHEN L.yahoopos<L2.yahoopos THEN 'down' ELSE 'nochange' END END FROM log L JOIN log L2 ON L.word=L2.word WHERE L.word='concrete' AND L2.word='concrete' AND L.mydate=DATEPOINT AND L2.mydate=(SELECT MAX(mydate) FROM log L3 WHERE word='concrete' AND mydate<DATEPOINT)

[edited by: FalseDawn at 5:51 pm (utc) on Aug. 25, 2006]

Looks pretty to me FD ;)

But I am a nerd so....