SHARE
TWEET

Untitled

a guest Jun 19th, 2019 62 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. records
  2.  
  3. rc_id     coach     runner        msg  
  4.     1     steve       bill    'hello'
  5.      
  6. runners
  7.  
  8. r_id     coach     runner   km_run    score
  9.    1     steve       mary      3.5     0.56
  10.    2     steve       anna      1.5     0.99
  11.    3     steve       john      6.5     0.78
  12.    4     steve       bill      1.5     0.21
  13.    5     steve       jess      6.2     0.95
  14.    6     steve       jack      2.5     0.42
  15.      
  16. SELECT
  17.   *
  18. FROM
  19.   (
  20.     SELECT
  21.       r_id,
  22.       q.coach,
  23.       q.runner,
  24.       km_run,
  25.       score,
  26.       ROW_NUMBER() OVER (PARTITION BY km_run) AS d,
  27.       CASE
  28.         WHEN
  29.           ROW_NUMBER() OVER (PARTITION BY q.coach
  30.     ORDER BY
  31.       q.priority, score DESC) = 1
  32.     THEN
  33.       records.msg
  34.       END
  35.       as records_msg
  36.     FROM
  37.       (
  38. (
  39.         SELECT
  40.           1 as priority, r_id, coach, runner, km_run, score
  41.         FROM
  42.           runners
  43.         WHERE
  44.           runners.runner = ANY('{jess}'::text[])
  45.           AND NOT runners.runner = ANY('{}'::text[]))
  46.         UNION
  47. (
  48.         SELECT
  49.           2 as priority, r_id, coach, runner, km_run, score
  50.         FROM
  51.           runners
  52.         WHERE
  53.           km_run <= '15'
  54.           AND coach = 'steve'
  55.         ORDER BY
  56.           km_run) LIMIT 500
  57.       )
  58.       AS q
  59.       RIGHT JOIN
  60.         records
  61.         ON q.coach = records.coach
  62.         AND q.coach = 'steve'
  63.     ORDER BY
  64.       q.priority,
  65.       score DESC
  66.   )
  67.   j
  68. WHERE
  69.   j.d < 100 LIMIT 6
  70.      
  71. r_id     coach     runner   km_run    score     d     records_msg
  72.    1     steve       jess      6.2     0.95     1           hello
  73.    2     steve       anna      1.5     0.99     2            null
  74.    3     steve       john      6.5     0.78     1            null
  75.    4     steve       mary      3.5     0.56     1            null
  76.    5     steve       jack      2.5     0.42     1            null
  77.    5     steve       jack      2.5     0.42     1            null
  78.      
  79. r_id     coach     runner   km_run    score     d     records_msg
  80.    1     steve       jess      6.2     0.95     ?           hello
  81.    2     steve       anna      1.5     0.99     ?            null
  82.    3     steve       jess      6.2     0.95     ?            null
  83.    4     steve       john      6.5     0.78     ?            null
  84.    5     steve       mary      3.5     0.56     ?            null
  85.    6     steve       bill      1.5     0.21     ?            null
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top