Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement