How can we return only unique records from table?-CodePudding

I am having a table structure like this

CREATE TABLE yourTable (
  `Source` VARCHAR(20),
  `Destination` VARCHAR(20),
  `Distance` Integer
);

INSERT INTO yourTable
  (`Source`, `Destination`, `Distance`)
VALUES
  ('Buffalo', 'Rochester', 2200),
  ('Yonkers', 'Syracuse', 1400),
  ('Cheektowaga', 'Schenectady', 600),
  ('Rochester', 'Buffalo', 2200)

How can we return only unique records for example as 'Buffalo' and 'Rochester' are present in 1 & 4 rows so one should be taken while retrieving.

I tried writing this query but here source and destination values are not correct for 3 rows Schenectady Cheektowaga

SELECT DISTINCT GREATEST(Source, Destination) as Source,  LEAST(Source, Destination) AS Destination, Distance
FROM yourTable

CodePudding user response：

Use two queries that you combine with UNION. One query returns the rows that are already unique, the other removes the duplicate from the rows that are duplicated in the other direction.

SELECT t1.Source, t1.Destination, t1.Distance
FROM yourTable AS t1
LEFT JOIN yourTable AS t2 ON t1.Source = t2.Destination AND t1.Destination = t2.Source
WHERE t2.Source IS NULL

UNION ALL

SELECT GREATEST(Source, Destination) AS s,  LEAST(Source, Destination) AS d, MAX(Distance) AS Distance
FROM yourTable
GROUP BY s, d
HAVING COUNT(*) > 1

DEMO

CodePudding user response：

Try this:

select * from yourTable group by greatest(source,destination);