how to get the sql data in reverse order of rows

i have a source table

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

​x
 
Source | Desti | DISTbng hyd 300bng chn 200chn bng 200hyd bng 300hyd pune 1000​

desired result

output—

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

 
Source || Desti || DISThyd | pune | 1000hyd | bng | 300chn | 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

 
select distinctcase when source<destination then source else destination end source,case when source<destination then destination else source end destination,distancefrom test_tableorder by distance DESC​

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

Answer

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;

 
SELECT DISTINCT    CASE WHEN source < destination THEN destination ELSE source END AS source,    CASE WHEN source < destination THEN source ELSE destination END AS destination,    distanceFROM test_tableORDER BY distance DESC;​

Advertisement

Answer