Ad

Select Records According To Data's Priority Of A Column - Mysql

connections_tbl

point1   point2   medium

ktm      pkr      air
pkr      ktm      road
pkr      baglung  road
baglung  palpa    road
ktm      brt      air
brt      ktm      road
baglung  pkr      train

required output

point1   point2   medium

ktm      pkr      air
pkr      baglung  train
baglung  palpa    road
ktm      brt      air

My problem is similar to this question.I want to do is create a list of unique rows based on the value of medium, with priority to air,train and road if all medium records exist for the same points. Note:Priority order is air>train>road.

Ad

Answer

You can use the functions least() and greatest() since points are interchangeable:

select t.point1, t.point2, t.medium
from (
  select *,
    row_number() over(
      partition by least(point1, point2), greatest(point1, point2) 
      order by field(medium, 'air', 'train', 'road')
    ) rn
    from connections_tbl 
) t
where t.rn = 1

See the demo.
Results:

| point1  | point2 | medium |
| ------- | ------ | ------ |
| baglung | palpa  | road   |
| baglung | pkr    | train  |
| ktm     | brt    | air    |
| ktm     | pkr    | air    |
Ad
source: stackoverflow.com
Ad