Geek Answers Handbook

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

I am trying to do what it seems to be a simple SQL operation, but I just cannot find the correct syntax to do this quickly. I am using SQLite.

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

I would like to extract for each objUid: objUid, minTime, frame value in minTime, maxTime, frame value in maxTime.

... and I would like to do it as quickly as possible.

I have it right now and it works, but if I select the “NATURAL JOIN” instructions (which means that I don't get the “frame” column), it's 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?

Thank!

+5
1

:

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
+2

More articles:


All Articles