How to query row with max value in a column in a referencing table?

Question

There are popular case, where is needed to select a row with max value (or min or whatever else aggregate function result) from a table. My case is more complex - it adds JOIN into the picture.

Here is an example:

CREATE TABLE spacecraft (
  id   serial PRIMARY KEY, 
  name text NOT NULL
);

CREATE TABLE launch (
  id            serial PRIMARY KEY,
  spacecraft_id int REFERENCES spacecraft(id) NOT NULL,
  started       timestamptz NOT NULL,
  success       bool NOT NULL
);

INSERT INTO spacecraft (id, name) VALUES 
    (1, 'Bebop'), 
    (2, 'Serenity'), 
    (3, 'Death Start');

INSERT INTO launch (spacecraft_id, started, success) VALUES 
    (1, 'January 8 04:05:06 2999 UTC', true), 
    (2, 'December 1 01:00:00 3432 UTC', true), 
    (3, 'February 15 00:00:00 4521 UTC', false), 
    (3, 'July 10 12:05:00 4525 UTC', true);

http://sqlfiddle.com/#!15/a1921

How to select last launch result (launch.success) for each spacecraft?

UPDATE #1

That's my current solution:

SELECT DISTINCT S.*, last_launch, L2.success FROM spacecraft AS S
LEFT OUTER JOIN (SELECT *, MAX(started) OVER (PARTITION BY spacecraft_id) AS last_launch FROM launch) AS L1
ON L1.spacecraft_id=S.id
LEFT OUTER JOIN launch AS L2
ON L2.started=L1.last_launch;

http://sqlfiddle.com/#!15/45618/38

score 1 · Answer 1 · answered Oct 11 '15 at 20:43

If you want to get only max of success, so you have add a condition to filter only success. and using Window Functions as you did:

select spacecraft.* , max(started) over (partition by spacecraft.id)  
from spacecraft 
    left outer join 
    launch 
    on spacecraft.id = spacecraft_id
where success

SQL FIDDLE

score 1 · Answer 2 · answered Oct 11 '15 at 20:47

Here is my solution.

SELECT s.id, s.name, l.last_started
FROM spacecraft AS s
INNER JOIN (SELECT spacecraft_id, MAX(started) AS last_started FROM launch WHERE success GROUP BY spacecraft_id) AS l
ON s.id = l.spacecraft_id

http://sqlfiddle.com/#!15/45618/50

score 1 · Accepted Answer · edited May 23 '17 at 12:14

Assuming you want all columns from spacecraft in the result, plus the latest launch and the success of the same row.

Faster for few launches per spacecraft:

SELECT s.*, l.last_launch, l.success
FROM   spacecraft s
LEFT   JOIN (
   SELECT DISTINCT ON (spacecraft_id)
          spacecraft_id, started AS last_launch, success
   FROM   launch
   ORDER  BY spacecraft_id, started DESC
   ) l ON l.spacecraft_id = s.id;

Faster for many launches per spacecraft:

SELECT s.*, l.last_launch, l.success
FROM   spacecraft s
LEFT   JOIN LATERAL (
   SELECT started AS last_launch, success
   FROM   launch
   WHERE  spacecraft_id = s.id
   ORDER  BY started DESC
   LIMIT  1
   ) l ON true;

LATERAL requires Postgres 9.3+.
LEFT JOIN to include spacecrafts without any launches. Else use JOIN.

The essential ingredient to make either of these queries fast is a multicolumn index on (spacecraft_id, started). For this particular query it would be beneficial to success to the index to allow index-only scans:

CREATE INDEX launch_foo_idx ON launch (spacecraft_id, started DESC, success);

SQL Fiddle.

Detailed explanation:

How to query row with max value in a column in a referencing table?

UPDATE #1

3 Answers3