how to get the sql data in reverse order of rows-CodePudding

i have a source table

Source | Desti | DIST
bng hyd 300
bng chn 200
chn bng 200
hyd bng 300
hyd pune 1000

desired result

output---

Source || Desti || DIST
hyd | pune | 1000
hyd | bng | 300
chn | bng | 200

i was trying something like this

select distinct
case when source<destination then source else destination end source,
case when source<destination then destination else source end destination,
distance
from test_table
order by distance DESC

is this is a simple way to write or any better ways to write ?

CodePudding user response：

We can use CASE expressions to find each distinct source destination pair:

SELECT DISTINCT
    CASE WHEN source < destination THEN destination ELSE source END AS source,
    CASE WHEN source < destination THEN source ELSE destination END AS destination,
    distance
FROM test_table
ORDER BY distance DESC;