What is the solution for this sql complex interview question?

Question

I am pretty good at sql. I have faced this question in interview and was unable to figure out the answer.

Question: There is a location table With 3 columns (source, destination, distance)as show below

Location:

Source - Destination - Distance
Hyderabad - Chennai - 700
Chennai - Hyderabad - 700
Hyderabad - Bangalore -650

As given above , Hyderabad to Chennai or Chennai to Hyderabad both mean the same as the distance will always be same.

So we need to write the query to so that the output should not have such kind of duplicate records.

In the above case output should be

Location:

Source - Destination - Distance
Hyderabad - Chennai - 700
Hyderabad - Bangalore -650

Answer 1

I think this does what you want under these conditions:

select l.*
from location l
where l.source < l.destination;

Answer 2

If your SQL DB/engine supports LEAST/GREATEST (it is an ISO syntax, so many of them do):

select  distinct
        least   (source, destination) as source
       ,greatest(source, destination) as destination
       ,distance

from    location

SQL Fiddle - Postgres
SQL Fiddle - MySQL
SQL Fiddle - Oracle

If not:

select  distinct
        case when source < destination then source else destination end as source
       ,case when source < destination then destination else source end as destination
       ,distance

from    location

SQL Fiddle - SQLite
SQL Fiddle - SQL Server