1

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.

Prajwal Siwakoti
  • 23
  • 6

2 Answers2

1

You can filter with correlated subquery:

select c.*
from connections_tbl c
where c.medium = (
    select c1.medium 
    from connections_tbl c1
    where c1.point1 = c.point1 and c1.point2 = c.point2
    order by field(medium, 'air', 'train', 'road')
    limit 1
) t

Alternatively, in MySQL 8.0, you can use row_number():

select point1, point2, medium
from (
    select 
        c.*,
        row_number() over(partition by point1, point2 order by field(medium, 'air', 'train', 'road')) rn
    from connections_tbl c
) t
where rn = 1
GMB
  • 216,147
  • 25
  • 84
  • 135
  • whats the meaning of t in the first query. – Prajwal Siwakoti Mar 11 '20 at 09:38
  • Both queries do the same thing, which is generate one record per `(point1, point2)` tuple according to the priority rules. The first query uses a correlated subquery that returns the top priority `medium` for each tuple. – GMB Mar 11 '20 at 09:42
  • when i write t it gives me an error and if dont write t, it gives output but not the expected output .Gives all the records from the table. – Prajwal Siwakoti Mar 11 '20 at 09:44
  • your second query worked with the pervious question. Thanks for that . Please check once again , i have modified the question , point1 value can be in point2 and viceversa. i.e `ktm pkr air` `pkr ktm road` – Prajwal Siwakoti Mar 11 '20 at 09:53
1

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    |
forpas
  • 160,666
  • 10
  • 38
  • 76