request help for subquery

[hik2sanity]

I have a table that records targets and the time it appears on a
display. What I would like to do is to report the time difference for
each individual target from the initial appearance to the subsequent
one, and the time difference from the subsequent one to the next, and
so on.

So how do I put these these all together to produce one query:

for each "select distinct target from display"
for number of rows -1 with target
"select timeX - timeY from (subquery for distinct target) where
(X,Y = subsequent, initial times)"

thanks for commenting

[strawberry]

hi********@hotm ail.com wrote:

I have a table that records targets and the time it appears on a
display. What I would like to do is to report the time difference for
each individual target from the initial appearance to the subsequent
one, and the time difference from the subsequent one to the next, and
so on.

So how do I put these these all together to produce one query:

for each "select distinct target from display"
for number of rows -1 with target
"select timeX - timeY from (subquery for distinct target) where
(X,Y = subsequent, initial times)"

thanks for commenting

It depends on how exactly your targets are identified, but a simple
solution might look like this (untested):

SELECT t2.target_id,(t 2.target_time-t1.target_time) AS difference
FROM targets AS t1
LEFT JOIN targets as t2 ON t2.target_id = (t1.target_id + 1)