Distincts in SQL query based on 2 column

Transact-SQL
1

Hi,
I have the table for train journey col 1 - From destinationn col 2 - To Destination col C distance
now i want to write a query to ge the unique journeys that are available?

for example there will be rows for
From - London To Paris
From Paris To London,

basically both are same trip, so wanted to get just one in my result?
how do i write a query to get this

Regards
Arvind

3

One of the following?

SELECT COUNT(*)/2
FROM
(
	SELECT [From],[To] FROM YourTable
	UNION
	SELECT [To],[From] FROM YourTable
)s;

SELECT COUNT(*)
FROM YourTable y1
WHERE NOT EXISTS
(
	SELECT * FROM yourTable y2
	WHERE y2.[From] = y1.[To]
	AND y2.[To] = y1.[From]
	AND y1.[From] > y2.[From]
);
5

Nice Vignesh,

6

Hi,

Thanks for your help, I am not getting the right result
here is the data

From To Distance
Milano Bergamo 47
Milano Lecco 44
Brescia Bergamo 44
Milano Brescia 82
Bergamo Lecco 32
Bergamo Milano 47
Bergamo Brescia 44

desired result should be
Origin Destination Distance
Bergamo Milano 47
Milano Lecco 44
Brescia Bergamo 44
Milano Brescia 82
Bergamo Lecco 32

only the last 2 were duplicates hence removed

7

James's query works:)

Thanks

8

do mind explaining the logic?

9

Because you want the actual rows and not just the count, the first query I posted would not work. The second query should.

The second query looks at each record and uses the NOT EXISTS clause to include that record only if there is no record which has the reverse (i.e, the From of the record matches the To of another record, and the To of the record matches the From of the second record).

If you just have those two conditions, the two records - London to Paris and Paris to London - will eliminate each other. But we want to keep one of those. So you have the additional check y1.[From] > y2.[From]. The From of one of the records has to be greater than the From of the other (string sort). So you eliminate one, and retain the other.

10

thank you very much for help

now i understand the logic

11

I know I'm a bit late, but here's an alternative:

select distinct
       case when [from]<[to] then [from] else [to] end as [from]
      ,case when [from]<[to] then [to] else [from] end as [to]
      ,distance
  from yourtable
1 Like