CODEWITHSUNDEEP
sqlsqlitesubquery
ereisch
ereisch

Reputation: 204

Multiple subquery criteria or looping in SQLite

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?

Upvotes: 0

Views: 222

Answers (1)

forpas
forpas

Reputation: 164174

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 | .... | ....

Upvotes: 1

Related Questions