MySQL query question

rtaft · 10-07-2008, 02:54 PM

Let's say I have a table that has the time and my name in it. Every minute an entry is added to the DB with the current time, and my name. Every so often I change my name. Using this setup (not the actual one but close to what I am dealing with), how can I select the times when my name changed? There are other attributes that change, which is why it is input frequently, but I just want to know when the name changes.

I can use:
SELECT MIN(time), name FROM table GROUP BY name;

Which works if I never use the same name twice. I'm looking for something that might work for when the name changes back to something it has been before.
IE
TIME | NAME
1 | Jim
2 | Jim
3 | Fred
4 | Fred
5 | Fred
6 | Jim
7 | Alex
8 | Joe
9 | Joe
....

Where this would result in
TIME | NAME
1 | Jim
3 | Fred
6 | Jim
7 | Alex
8 | Joe

With my query above, '6 | Jim' is missing

CRC123 · 10-07-2008, 03:13 PM

I haven't done SQL in a long time, but if the TIME column always increments by one (or if you have a column that always increments by 1 chronologically), then you could just search for rows where the name is different than the last:

Code:

SELECT * 
FROM table a
WHERE a.TIME != 0 AND a.NAME != (SELECT b.NAME FROM table b WHERE b.TIME = (a.TIME - 1))

DISCLAIMER:
I assume that TIME starts at 0 and increments by 1 for every entry (if it does not, add a column that does this)
I have not tested this
I think this will work but I'm note sure if 'a.TIME - 1' is proper SQL syntax.

rtaft · 10-07-2008, 03:26 PM

Thanks, this actually worked! I had to change the start time, and the increment to 60000, but it works!