SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE locations  (
  `simulation_id` INTEGER,
  `sprite_id` INTEGER,
  `time` INTEGER,
  `x` REAL,
  `y` REAL
);
✓
INSERT INTO locations 
  (`simulation_id`, `sprite_id`, `time`)
VALUES
  ('1', '1', '0.0'),
  ('1', '2', '0.0'),
  ('1', '1', '1.0'),
  ('1', '2', '1.0'),
  ('1', '3', '1.0'),
  ('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');
✓
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
)


simulation_id
sprite_id
time
x
y


1
3
1
null
null

2
3
3
null
null


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


simulation_id
sprite_id
time
x
y


1
3
1
null
null

2
3
3
null
null

simulation_id	sprite_id	time	x	y
1	3	1	null	null
2	3	3	null	null

simulation_id	sprite_id	time	x	y
1	3	1	null	null
2	3	3	null	null