Get row before SELECT query

Question

I have a table called "mytable". The columns are

Time_Stamp (datetime) PK
Time_stamp_ms (int) PK
data1 (int)
data2 (int)
data3 (int)
data4 (int) 
data5 (int)
data6 (int)
cycle (int)
name (varstring)

I want to order by Time_Stamp and Time_stamp_ms (I know how to do this from another question) and then each time cycle reaches 1, I want to get the Time_Stamp and Time_Stamp_ms from the previous row. Cycle is 1,2,3,4......n Means it will always increment by 1.

This table will problably have millions and millions of rows.

Also no PHP.

There is a sample of my table:

Time_Stamp              Time_Stamp_ms   d1      d2      d3      d4      d5      d6      cycle   name    

2014-04-24 09:09:37         765         5555    4444    3333    2222    1111    123     1       name
2014-04-24 09:09:37         845         5555    4444    3333    2222    1111    123     2       name
2014-04-24 09:09:37         925         5555    4444    3333    2222    1111    123     3       name
2014-04-24 09:09:38         5           5555    4444    3333    2222    1111    123     4       name
2014-04-24 09:09:38         85          5555    4444    3333    2222    1111    123     5       name
2014-04-24 09:09:38         165         5555    4444    3333    2222    1111    123     6       name
2014-04-24 09:09:38         245         5555    4444    3333    2222    1111    123     7       name
2014-04-24 09:09:38         325         5555    4444    3333    2222    1111    123     8       name
2014-04-24 09:09:38         405         5555    4444    3333    2222    1111    123     9       name
2014-04-24 09:09:38         485         5555    4444    3333    2222    1111    123     10      name
2014-04-24 09:09:38         565         5555    4444    3333    2222    1111    123     11      name
2014-04-24 09:09:38         645         5555    4444    3333    2222    1111    123     12      name
2014-04-24 09:09:38         725         5555    4444    3333    2222    1111    123     13      name
2014-04-24 09:09:38         805         5555    4444    3333    2222    1111    123     1       name
2014-04-24 09:09:38         885         5555    4444    3333    2222    1111    123     2       name
2014-04-24 09:09:38         965         5555    4444    3333    2222    1111    123     3       name
2014-04-24 09:09:39         45          5555    4444    3333    2222    1111    123     4       name
2014-04-24 09:09:39         125         5555    4444    3333    2222    1111    123     5       name
2014-04-24 09:09:39         205         5555    4444    3333    2222    1111    123     6       name
2014-04-24 09:09:39         285         5555    4444    3333    2222    1111    123     1       name
2014-04-24 09:09:39         365         5555    4444    3333    2222    1111    123     2       name
2014-04-24 09:09:39         445         5555    4444    3333    2222    1111    123     3       name
2014-04-24 09:09:39         525         5555    4444    3333    2222    1111    123     4       name
2014-04-24 09:09:39         605         5555    4444    3333    2222    1111    123     5       name
2014-04-24 09:09:39         685         5555    4444    3333    2222    1111    123     6       name
2014-04-24 09:09:39         765         5555    4444    3333    2222    1111    123     1       name
2014-04-24 09:09:39         845         5555    4444    3333    2222    1111    123     2       name
2014-04-24 09:09:39         925         5555    4444    3333    2222    1111    123     3       name

Should return me:

    Time_Stamp              Time_Stamp_ms   d1      d2      d3      d4      d5      d6      cycle   name    


2014-04-24 09:09:38         725         5555    4444    3333    2222    1111    123     13      name
2014-04-24 09:09:39         205         5555    4444    3333    2222    1111    123     6       name
2014-04-24 09:09:39         685         5555    4444    3333    2222    1111    123     6       name

Answer 1

As said in the comments, you really do need a field that indicates the order of the rows. A pkey int primary key auto_increment field does not guarantee that the newest row always has the largest id, so strictly speaking that does not work 100% of the time. A column containing exact insert time would do.

Assuming though (falsely i know), that your value field is one that can be sorted with, this query will get you every row that comes before a id=1. To get a proper result, create a field that is in order, and replace value with that field in the two order by clauses

updated query: http://sqlfiddle.com/#!2/9cf7d1/1/0

SELECT Time_Stamp, Time_stamp_ms, cycle FROM (
    SELECT 
        COALESCE((@preVal=1), 0) AS afterOne, 
        m.*, 
        @preVal:=m.cycle  
    FROM mytable as m, 
    (SELECT @preVal:=NULL) AS d 
    ORDER BY Time_Stamp desc, Time_stamp_ms desc
) t 
WHERE afterOne = 1 
ORDER BY Time_Stamp, Time_stamp_ms;

One additional note. If you are dealing with a big dataset, you may find drastically improved performance by inserting the inner query in to a temporary table, indexing afterOne, and then selecting the final result. MySQL is notorious for being slow with subquerys.

PS. hmm, i see now that i may have chosen poorly, afterOne really means before when ordered ascending. Oh well, its a placeholder anyways, can be named anything that makes sense.