Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL select the nearest values with join

There are 2 tables:

reference:

id  | score  | value | type_id
1   | 0      | 10    | 1
2   | 1      | 20    | 1
3   | 2      | 30    | 1
..  | ..     | ..    | ..

result:

id  | score  | type_id
1   | 2      | 1
2   | 7      | 2
3   | 0      | 3

I want to get value from refernce table for each type_id depending on the score in result table.

The query:

SELECT ref.score, ref.value, ref.type_id
FROM `refernce` ref
JOIN `result` res
    ON ref.type_id = res.type_id
WHERE res.score >= ref.score
GROUP BY ref.type_id
ORDER BY ref.id DESC;

The output in this case should be:

score | value   | type_id
0     | 8       | 3
3     | 25      | 2
2     | 30      | 1

But this is the actual output:

score | value   | type_id
0     | 8       | 3
0     | 5       | 2
0     | 10      | 1

So if the score in result table exists in refernce the value for this score and if the score in result table is greater than in the refernce table the value of the greater score which is 3 should be returned.

Here is a fiddle: http://sqlfiddle.com/#!9/ecf1e3/1

Tags:

2

Answers


  1. 0

    use MAX 🙂 to get the highest value … and then join again based on those two values …

    SELECT ref.score 
, subsel.max_val 
, subsel.type_id
FROM (
  SELECT res.score, max(ref.value) as max_val, ref.type_id
  FROM `refernce` ref
  JOIN `result` res
      ON ref.type_id = res.type_id
  WHERE res.score >= ref.score
  GROUP BY res.score, ref.type_id
) subsel
JOIN `refernce` ref
ON ref.type_id = subsel.type_id
AND ref.value = subsel.max_val 
WHERE 1=1
ORDER BY 3 desc;

    In case you are using some more sophisticated DB (Postgre, Oracle, ..)

    You can use window functions to partition and order the rows .. and then only filter in the subsequent select the ones you need

    Example (not tested)

    SELECT score_ref
, value
, type_id
FROM (
  SELECT res.score as score_res
  , ref.score as score_ref
  , ref.value
  , ref.type_id
  , row_number() over (partition by ref.type_id order by ref.value desc) as row_order
  FROM `refernce` ref
  JOIN `result` res
      ON ref.type_id = res.type_id
  WHERE res.score >= ref.score
  GROUP BY ref.type_id
)
WHERE row_number = 1
    Login or Signup to reply.
  2. 0

    A solution that used only one subquery from joining the reference and result tables, where the score and value columns are converted to fixed-length hexadecimal string representations, combined and the max aggregation function is applied to them, after which the hexadecimal strings are converted back to integers.

    select
  conv(substr(binstr, 1, 6), 16, 10) as score,
  conv(substr(binstr, -12), 16, 10) as `value`,
  type_id
from (
  select
    max(concat(
      lpad(conv(cast(ref.score as binary), 10, 16), 6, '0'),
      lpad(conv(cast(ref.`value` as binary), 10, 16), 12, '0')
    )) as binstr,
    ref.type_id
  from result as res
  join reference as ref
  on res.type_id = ref.type_id and
     res.score >= ref.score
  group by ref.type_id, res.score
) as t;

    Result:

    +-------+-------+---------+
| score | value | type_id |
+-------+-------+---------+
|     2 |    30 |       1 |
|     3 |    25 |       2 |
|     0 |     8 |       3 |
+-------+-------+---------+

    SQL Fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search