SQL: Quickly getting the value of column B wherever column A is minimum

Question

I'm trying to do what seems like it should be a simple SQL operation, but I'm just not finding the right syntax to do it quickly. I'm using SQLite.

The basic problem is that I have a table whose primary key is (objUid, time). It contains the columns objUid, time, and frame. For the purposes of this question, frame is an opaque value.

I would like to extract out for each objUid: objUid, minTime, value of frame at minTime, maxTime, value of frame at maxTime.

...and I'd like to do it as quickly as possible.

I have this right now, which works, but if I take out the "NATURAL JOIN" statements (which means I don't get the "frame" column), things are about twice as fast.

SELECT * FROM (
    SELECT * FROM (
        SELECT objUid, min(time) as minTime, max(time) as maxTime FROM motion GROUP BY objUid
    ) NATURAL JOIN (
        SELECT objUid, time as minTime, frame as minFrame FROM motion
    )
) NATURAL JOIN (SELECT objUid, time as maxTime, frame as maxFrame FROM motion)

Any ideas?

Thanks!

Answer 1

Use:

SELECT x.objuid,
       y.time,
       y.frame,
       z.time,
       z.frame
  FROM (SELECT m.objuid,
               MIN(m.time) AS min_time,
               MAX(m.time) AS max_time
          FROM MOTION m
      GROUP BY m.objuid) x
  JOIN MOTION y ON y.objuid = x.objuid
               AND y.time = x.min_time
  JOIN MOTION z ON z.objuid = x.objuid
               AND z.time = x.max_time