Is it possible to perform a "foreach"-like query in a single SQL statement? My specific implementation is using SQLite, so suggestions with compatible syntax are preferred.
Example: Let's say we have a table similar to the following:
simulation_id | sprite_id | time | x | y
1 1 0.0 0.0 0.0
1 2 0.0 5.5 1.6
1 1 1.0 0.1 0.0
1 2 1.0 5.5 1.5
1 3 1.0 9.9 4.1
1 1 2.0 ...
1 2 2.0
1 3 2.0
2 1 0.0
2 1 1.0
2 1 2.0
2 2 2.0
2 1 3.0
2 2 3.0
2 3 3.0
...
The key takeaway from the table is that not all "sprite_id" entries may be instantiated at t=0.0.
I would like to retrieve the positions for all sprites at the first instance sprite_id=3 appears in each simulation. So, my ideal query would retrieve the positions for sprites 1, 2, and 3 at t=1.0 for simulation_id=1, and t=3.0 for simulation_id=2.
I believe the query would be something along the lines of:
SELECT simulation_id, sprite_id, time, x, y
FROM locations WHERE time = (SELECT MIN(time) FROM locations WHERE sprite_id = 3)
...however I would need this run for each simulation_id in the table; is this even possible in a single SQL query?
Your can use a correlated subquery in the WHERE clause:
SELECT l.*
FROM locations l
WHERE l.sprite_id = 3
AND time = (
SELECT MIN(time)
FROM locations
WHERE simulation_id = l.simulation_id AND sprite_id = l.sprite_id
)
Or with ROW_NUMBER() window function:
SELECT simulation_id, sprite_id, time, x, y
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY simulation_id ORDER BY time) rn
FROM locations
WHERE sprite_id = 3
)
WHERE rn = 1
See the demo.
Results:
> simulation_id | sprite_id | time | x | y
> ------------: | --------: | ---: | :--- | :---
> 1 | 3 | 1.0 | .... | ....
> 2 | 3 | 3.0 | .... | ....