records

rc_id     coach     runner        msg
    1     steve       bill    'hello'

runners

r_id     coach     runner   km_run    score
   1     steve       mary      3.5     0.56
   2     steve       anna      1.5     0.99
   3     steve       john      6.5     0.78
   4     steve       bill      1.5     0.21
   5     steve       jess      6.2     0.95
   6     steve       jack      2.5     0.42

SELECT
  *
FROM
  (
    SELECT
      r_id,
      q.coach,
      q.runner,
      km_run,
      score,
      ROW_NUMBER() OVER (PARTITION BY km_run) AS d,
      CASE
        WHEN
          ROW_NUMBER() OVER (PARTITION BY q.coach
    ORDER BY
      q.priority, score DESC) = 1
    THEN
      records.msg
      END
      as records_msg
    FROM
      (
(
        SELECT
          1 as priority, r_id, coach, runner, km_run, score
        FROM
          runners
        WHERE
          runners.runner = ANY('{jess}'::text[])
          AND NOT runners.runner = ANY('{}'::text[]))
        UNION
(
        SELECT
          2 as priority, r_id, coach, runner, km_run, score
        FROM
          runners
        WHERE
          km_run <= '15'
          AND coach = 'steve'
        ORDER BY
          km_run) LIMIT 500
      )
      AS q
      RIGHT JOIN
        records
        ON q.coach = records.coach
        AND q.coach = 'steve'
    ORDER BY
      q.priority,
      score DESC
  )
  j
WHERE
  j.d < 100 LIMIT 6

r_id     coach     runner   km_run    score     d     records_msg
   1     steve       jess      6.2     0.95     1           hello
   2     steve       anna      1.5     0.99     2            null
   3     steve       john      6.5     0.78     1            null
   4     steve       mary      3.5     0.56     1            null
   5     steve       jack      2.5     0.42     1            null
   5     steve       jack      2.5     0.42     1            null

r_id     coach     runner   km_run    score     d     records_msg
   1     steve       jess      6.2     0.95     ?           hello
   2     steve       anna      1.5     0.99     ?            null
   3     steve       jess      6.2     0.95     ?            null
   4     steve       john      6.5     0.78     ?            null
   5     steve       mary      3.5     0.56     ?            null
   6     steve       bill      1.5     0.21     ?            null