Get row before SELECT query

Question

I have a table called mytable. Columns

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 every time the loop reaches 1, I want to get Time_Stamp and Time_Stamp_ms from the previous line. The cycle 1,2,3,4 ...... n means that it always increases by 1.

This table is likely to have millions and millions of rows.

Also no PHP.

There is an example 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 

Gotta get me back:

  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 need a field that indicates the order of the lines. The pkey int primary key auto_increment field does not guarantee that the newest line always has the largest identifier, therefore, strictly speaking, it does not work in 100% of cases. A column containing the exact insertion time will do.

Assuming though (falsely I know) that your value field is the one that can be sorted, this query will give you every line that comes up to id = 1. To get the correct result, create a field that is in order and replace value on this field in two sentences by order by

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 more note. If you are dealing with a large dataset, you can get significantly improved performance by inserting an internal query into a temporary table, indexing afterOne, and then selecting the final result. MySQL is famous for its slow query with subqueries.

PS. hmm, now I see that I might have chosen poorly, afterOne really means before when ordering ascending. Well, well, its placeholder can in any case be called what makes sense.

Answer 2

As mcalex said

You do not have a primary key. 2. The string order of the data MUST NOT matter. If you want these lines to be ordered, you need a field to help with this. THEN, you can request a string before a specific string

try it

 SELECT * from ( Select @prev As previous,@pid as `Previous id`,@pid := e.id As `id` ,@prev := e.value As current From ( Select @prev := null,@pid := 0 ) As i,tbl As e ) x Where id=1 And Previous is not null; 

Demo Screenshot

---

Exit

 +---------------------------------------------------+ | PREVIOUS | PREVIOUS_ID | Current_ID | CURRENT | +---------------------------------------------------+ | C | 3 | 1 | D | | F | 3 | 1 | G | | X | 4 | 1 | J | +---------------------------------------------------+ 

Answer 3

My first choice would probably be to use one of the above sentences generating a sequence number. However, with a large number of records, creating such a sequence can be slow (especially if you ignore more records).

However, another option is to make a connection. This is useless since you have 2 columns to determine which of the previous entries.

Not verified, but something like this: -

 SELECT a.*, b.Time_Stamp, b.Time_stamp_ms FROM ( SELECT a.Time_Stamp, a.Time_stamp_ms, a.cycle, MAX(DATE_ADD(b.Time_Stamp, INTERVAL b.Time_stamp_ms MICROSECONDS)) AS latest_prev_record FROM mytable a INNER JOIN mytable b ON DATE_ADD(a.Time_Stamp, INTERVAL a.Time_stamp_ms MICROSECONDS) > DATE_ADD(b.Time_Stamp, INTERVAL b.Time_stamp_ms MICROSECONDS) WHERE a.cycle = 1 GROUP BY a.Time_Stamp, a.Time_stamp_ms, a.cycle ) Sub1 INNER JOIN mytable a ON a.Time_Stamp = Sub1.Time_Stamp, AND a.Time_stamp_ms = Sub1.Time_stamp_ms, AND a.cycle = Sub1.cycle INNER JOIN mytable b ON DATE_ADD(b.Time_Stamp, INTERVAL b.Time_stamp_ms MICROSECONDS) = Sub1.latest_prev_record 

This can be made much simpler if you only need timestamps and other data, and if you have one combined date / time / millisecond field (you can just use the subquery). Even easier, if you had all the records that have a consistent id field (i.e., Guaranteed to be in that order).

EDIT - Simplified if you only want to return the last record before loop 1: -

 SELECT z.* FROM ( SELECT a.Time_Stamp, a.Time_stamp_ms, MAX(DATE_ADD(b.Time_Stamp, INTERVAL b.Time_stamp_ms MICROSECOND)) AS latest_prev_record FROM mytable a INNER JOIN mytable b ON DATE_ADD(a.Time_Stamp, INTERVAL a.Time_stamp_ms MICROSECOND) > DATE_ADD(b.Time_Stamp, INTERVAL b.Time_stamp_ms MICROSECOND) WHERE a.cycle = 1 GROUP BY a.Time_Stamp, a.Time_stamp_ms ) Sub1 INNER JOIN mytable z ON DATE_ADD(z.Time_Stamp, INTERVAL z.Time_stamp_ms MICROSECOND) = Sub1.latest_prev_record 

EDIT again.

You can add a decimal field for the combined timestamp (add an index for it) and fill it with: -

 update `mytable` set `timestamp_full` = UNIX_TIMESTAMP(`Time_Stamp`) + (`Time_stamp_ms` / 1000) 

then you can use the following SQL to get the required records: -

 SELECT z.* FROM ( SELECT a.timestamp_full, MAX(b.timestamp_full) AS latest_prev_record FROM mytable a INNER JOIN mytable b ON a.timestamp_full > b.timestamp_full WHERE a.cycle = 1 GROUP BY a.timestamp_full ) Sub1 INNER JOIN mytable z ON z.timestamp_full = Sub1.latest_prev_record 

Answer 4

If this is just a small table you are working with (less than 10,000 rows), I think the best solution is to select everything and select the rows “manually” (= in the PHP loop). This will certainly be much faster than any SQL-based solution, since you only select identifiers and a primary key to select the result rows.

Speaking of a strictly SQL-based solution, you need a stored procedure and a cursor to go through the result set (this allows you to take a step back) - but this is not very effective, since you need to query the entire table and perform the mapping one at a time. Index-based queries can NOT do this , so any SQL solution you get will go all over the table (do a “full scan”) and therefore will not be fast.

And YES, the previous answers are right in that the order of the lines just doesn't matter. They are kind of “random,” or at least you should look at them as if they were. (Even if you do ALTER TABLE ... ORDER BY, you cannot be sure that after the next operation that modifies one row.)

Answer 5

Sent as another answer, as it gets more complicated, and it is more of a conversation through an additional option.

It would be easier to make a join if there was an indexed column to check the last record or check the last group of loops.

If you add a column for the cycle number, you can fill it first: -

 SET @cycle_no = 0; UPDATE mytable SET cycle_no=@cycle_no:=@cycle_no + 1 WHERE cycle = 1 ORDER BY time_stamp, time_stamp_ms; 

then

 UPDATE mytable a SET a.cycle_no = (SELECT MAX(b.cycle_no) FROM mytable b WHERE a.time_stamp < b.time_stamp OR (a.time_stamp a.time_stamp < b.time_stamp b.time_stamp AND a.time_stamp_ms < b.time_stamp_ms )) WHERE a.cycle != 1 

The first one fills the_no loop for the eacg 1 loop, and the second fills all the other rows of the no_cycle value

You can keep it populated with the following trigger (maybe a more efficient way to do this).

 CREATE TRIGGER insert_mytable BEFORE INSERT ON mytable FOR EACH row SET NEW.cycle_no = IF(NEW.cycle = 1, (SELECT MAX(cycle_no) + 1 FROM mytable WHERE cycle = 1 ), (SELECT MAX(cycle_no) FROM mytable WHERE cycle = 1 )); 

Then you can get the latest values ​​like this (which relies only on cycle_no, increasing by 1): -

 SELECT z.* FROM ( SELECT b.cycle_no, MAX(b.cycle) FROM mytable a INNER JOIN mytable b ON b.cycle_no = (a.cycle_no - 1) WHERE a.cycle = 1 GROUP BY b.cycle_no ) Sub1 INNER JOIN mytable z ON z.cycle_no = Sub1.cycle_no 

In the test data, I shot down (~ 7.5 million records), it took ~ 53 seconds. Not sure if this will be useful to you.