Resolving the last column value change in SQL

Question

How can I get the date of the last change in the value in one column with one SQL query?

Possible situation with the database:

Date State 2012-11-25 state one 2012-11-26 state one 2012-11-27 state two 2012-11-28 state two 2012-11-29 state one 2012-11-30 state one 

Thus, the result should return 2012-11-29 as the last state of change. If I group by state value, I will get the date the first time I have this state in the database.

The query will group the table by state and display the state and in the date field the most recent date created from this state.

From this input, the output will be

 Date State 2012-11-30 state one 2012-11-28 state two 

Answer 1

This will give you the last state:

 -- Query 1 SELECT state FROM tableX ORDER BY date DESC LIMIT 1 ; 

Encapsulating the above, we can use it to get the date just before the last change:

 -- Query 2 SELECT t.date FROM tableX AS t JOIN ( SELECT state FROM tableX ORDER BY date DESC LIMIT 1 ) AS last ON last.state <> t.state ORDER BY t.date DESC LIMIT 1 ; 

And then use this to find the date (or the entire row) where the last change occurred:

 -- Query 3 SELECT a.date -- can also be used: a.* FROM tableX AS a JOIN ( SELECT t.date FROM tableX AS t JOIN ( SELECT state FROM tableX ORDER BY date DESC LIMIT 1 ) AS last ON last.state <> t.state ORDER BY t.date DESC LIMIT 1 ) AS b ON a.date > b.date ORDER BY a.date LIMIT 1 ; 

Tested in SQL-Fiddle

---

And a solution using MySQL variables:

 -- Query 4 SELECT date FROM ( SELECT t.date , @r := (@s <> state) AS result , @s := state AS prev_state FROM tableX AS t CROSS JOIN ( SELECT @r := 0, @s := '' ) AS dummy ORDER BY t.date ASC ) AS tmp WHERE result = 1 ORDER BY date DESC LIMIT 1 ; 

Answer 2

 SELECT MAX(DATE) FROM YOUR_TABLE 

Above answer does not seem to satisfy OP requirements.

UPDATED RESPONSE AFTER INSERT / UPDATE TRIGGER

 DELCARE @latestState varchar; DELCARE @latestDate date; CREATE TRIGGER latestInsertTrigger AFTER INSERT ON myTable FOR EACH ROW BEGIN IF OLD.DATE <> NEW.date THEN SET @latestState = NEW.state SET @latestDate = NEW.date END IF END ; CREATE TRIGGER latestUpdateTrigger AFTER UPDATE ON myTable FOR EACH ROW BEGIN IF OLD.DATE = NEW.date AND OLD.STATE <> NEW.STATE THEN SET @latestState = NEW.state SET @latestDate = NEW.date END IF END ; 

You can use the following query to get the latest added / updated record:

 SELECT DATE, STATE FROM myTable WHERE STATE = @latestState OR DATE = @latestDate ORDER BY DATE DESC ; 

Results:

 DATE STATE November, 30 2012 00:00:00+0000 state one November, 28 2012 00:00:00+0000 state two November, 27 2012 00:00:00+0000 state two 

The above query results should be limited to 2, 3 or n based on what you need.

Honestly, it looks like you want to get max from both columns based on your sample data. Assuming your state only increases with the date. Only I would like state be integer : D Then combining the two maximum subqueries on both columns would resolve this easily. However, the regular expression for the row can find what is in the max column in the state. Finally, this approach requires limit x . However, it still has a “strong” hole tube. In any case, it took me a while to figure out what you need: $

Answer 3

If you add another “modified datetime” column, you can populate it using the update trigger that inserts NOW (). If you request the ordering of tables in a modified column, it will be completed first.

 CREATE TRIGGER `trigger` BEFORE UPDATE ON `table` FOR EACH ROW BEGIN SET ROW.changed = NOW(); END$$ 

Answer 4

Try the following:

 Select MAX(`Date`), state from mytable group by state 

Answer 5

I believe this is the answer:

 SELECT DISTINCT State AS State, `Date` FROM Table_1 t1 WHERE t1.`Date`=(SELECT MAX(`Date`) FROM Table_1 WHERE State=t1.State) 

... and test:

http://sqlfiddle.com/#!2/8b0d8/5

Answer 6

If you used postgres, you could compare different rows in the same table using "LEAD .. OVER". I could not find the same functionality in mysql.

A little hairy, but I think this will do:

 select min(t1.date) from table_1 t1 where (select count(distinct state) from table_1 where table_1.date>=t1.date)=1 

Basically, it queries the first time that no state changes are found for any later values. Be careful, perhaps this query is very scalable for large datasets ....

Answer 7

I think your best bet here is analytic functions. Try this - it should be good in performance:

 SELECT * FROM test WHERE my_date = (SELECT MAX (my_date) FROM (SELECT MY_DATE FROM ( SELECT MY_DATE, STATE, LAG (state) OVER (ORDER BY MY_DATE) lag_val FROM test ORDER BY MY_DATE) a WHERE state != lag_val)) 

In the internal selection, the LAG function gets the previous value in the STATE column, and in the external select I mark the date of change - those for which the delay value differs from the current state value. And outside, I get the latest date from those change dates ... I hope this is what you need.