0

I have a table like this:

strt           end         no of km
------------------------------------
California     India         9000
Singapore      India         3000
India          Frankfurt     6700
Frankfurt      Singapore     4500
India          Indonesia     3000
India          California    9000
India          Singapore     3000
Frankfurt      India         6700
Singapore      Frankfurt     4500
Indonesia      India         3000

I need to get distinct rows from this by considering start and end point (we should get single entry even If we interchange the start and end values ). I tried concat the columns start and end as a list and sort the elements in the list and then applied distinct.

Is there any SQL query for this? Thanks in advance.

Output should be like this

strt          end       no of km
-------------------------------
California    India      9000
Singapore     India      3000
India         Frankfurt  6700
Frankfurt     Singapore  4500
India         Indonesia  3000
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sri123
  • 3
  • 4
  • Can you please add your ideal output? The question is not clear to me – MFR Aug 24 '22 at 05:01
  • It should return single row even if start and end values are interchanged. Distance from India to California is same as California to India. So if that kind of entries are available, it should result only one row.... – sri123 Aug 24 '22 at 05:11

1 Answers1

0

Welcome to SO!

The column could not be named end since that is a reserve word in SQL, so I used dest instead. And with that I could create this query that do solve your problem:

select
    distinct
    case when strt>dest then dest else strt end as strt,
    case when strt>dest then strt else dest end as dest,
    nr_of_km
from data
order by 1,2;

It will swap strt and dest if needed and and then use distinct.

NOTE: If there is an error in the data so that the distance differs between the directions for a pair then the query will produce two rows for that pair.

NOTE 2: The sorting distincts between upper- and lower-case characters. So if you need the query to treat a and A as the same character when comparing you could do something like this:

case when lower(strt)>lower(dest) then dest else strt end as strt,
...
UlfR
  • 4,175
  • 29
  • 45
  • Thanks much, its working.... What I am wondering is, on what basis < and > symbols works on string columns... were they calculated on hash value or str length. Would be great if you can help me in understanding – sri123 Aug 24 '22 at 08:11
  • `>` is "greater than". In other words: it does compare the strings to see which comes first if sorted lexically. It has nothing to do with hash or length, its purely based or how you regularly sort words. – UlfR Aug 24 '22 at 08:59