Select Second Newest Record Within A Group

[kd2017]

I have table [A] that has many of table [B]. Table [B] has a date field [d], and the fields I'm really after f1 and f2 (and, of course, a foreign key field [a_id]).




For each row in table [A], or grouped by [a_id], I need to select the second record in table [B] ordered by [B]'s date field [d]. I say record because I do need to return 2 fields from table [B] where [B]'s date is 2nd in it's group.

What's the most efficient way to accomplish this?

The best I've come up with is to create a two nested subqueries for each value I need to return. This seems inefficient.

SELECT
  A.*,
  (
    SELECT B1.f1
    FROM B AS B1
    WHERE B1.a_id=A.a_id
    AND B1.d <
    (
      SELECT Max(B2.d)
      FROM B AS B2
      WHERE B2.a_id=A.a_id
    )
  ) AS B_f1,
  (
    SELECT B1.f2
    FROM B AS B1
    WHERE B1.a_id=A.a_id
    AND B1.d <
    (
      SELECT Max(B2.d)
      FROM B AS B2
      WHERE B2.a_id=A.a_id
    )
  ) AS B_f2
FROM A

As you can see I'm calling subqueries multiple times to return fields from the same row.

[ArviLaanemets]

Create a named query p.e.
qTop1B = SELECT b_id, MAX(d) FROM B GROUP BY b_id;

Create another named query p.e.
qTop2B = SELECT b_id, MAX(d) FROM B b LEFT JOIN qTop1B b1 ON b1.b_id = b.b_id WHERE b.d < b1_d GROUP BY b.b_id;
(i'm using LEFT JOIN for case there is only one row for some b_id);

Now create your query using JOIN's (left/right inner joins or outer join depending you have one-to-many or one-to-one relationships) to link A, B, and qTop2B together. You never know, but there is a possiblility that with joined tables they are queried at start of query once, instead for every row in A as with your current syntax.