For the table:

index timestamp real 1 idx1-1 0,1 2 idx2-1 0,2 3 idx3-1 0,1 4 idx4-1 0,3 1 idx1-2 0,4 2 idx2-2 0,2 4 idx4-2 0,5 3 idx3-2 0,6 1 idx1-3 0,1 3 idx3-3 0,3 4 idx4-3 0,4 2 idx2-3 0,1

In the timestamp column, the time is in milliseconds (for convenience, it showed how index X is the order for the index).

Is it possible to get a table of the form from this table:

 index div 1 столбец real cтроки idx1-3 / столбец real cтроки idx1-2 2 столбец real cтроки idx2-3 / столбец real cтроки idx2-2 3 столбец real cтроки idx3-3 / столбец real cтроки idx3-2 4 столбец real cтроки idx4-3 / столбец real cтроки idx4-2

Those. for index X, the last line sorted by timestamp divided by the penultimate

    1 answer 1

    We get the last lines, after which we find for each of them all the previous ones in the group and again leave only the last ones.

     select Prev."index", Last.real / Prev.real from Table1 Prev join ( select * from Table1 group by "index" having timestamp=max(timestamp) ) Last on Prev."index"=Last."index" and Prev.timestamp < Last.timestamp group by Prev."index" having Prev.timestamp=max(Prev.timestamp);

    Sample at sqliteonline.com

    • Thank. This is it :-). and if it is necessary to divide not the last and the penultimate, but let's say the last and the fifth from the end? - Igor
    • @ Igor, then we get the last one like this, and the fifth from the end value does not join to it, but we get right in the selection list by a subquery like (select real from table1 where ... order by timestamp desc limit 5,1) - Mike
    • Thank. It is not clear yet. But I think I'll figure it out further. :-) - Igor