This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(313.6, 57.00000000000001%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ER%3C/text%3E%3C/svg%3E)
Hi Team,
find the unique routes
Source:
`
output:
Hi @Rupa ,
Could you please validate all the answers so far and provide any update?
If all are not working, please provide more sample data and expected output.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!
Best regards,
Melissa
Two solutions:
CREATE TABLE #routes (src char(1), dest char(1))
go
INSERT #routes(src, dest)
VALUES('C', 'H'),
('H', 'C'),
('K', 'H'),
('H', 'K'),
('K', 'B')
go
SELECT DISTINCT least(src, dest), greatest(src, dest)
FROM #routes
go
SELECT DISTINCT IIF(src < dest, src, dest),
IIF(src > dest, src, dest)
FROM #routes
The one with least and greatest only runs on Azure SQL Database.
Hi @Rupa ,
Welcome to Microsoft Q&A!
Reusing Erland's DDL.
Please also refer below which has the same output as your expected one:
select src source,dest destination from (
select ROW_NUMBER() over (partition by (IIF(src<dest,src,dest)+IIF(src<dest,dest,src)) order by (select 1)) rn,*
from #routes) a where rn=1
order by src
Output:
source destination
C H
K H
K B
Best regards,
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
First, I must clarify that This is good solution which fit the OP request.
I just want to remind that this solution is not deterministic which mean it might return different results (all will fit the OP request but not necessarily the same)
Explanation:
When we are using small tables the server usually returns the rows in the same order that we inserted them, but this is not guaranteed!
It is well documented that the table is a SET of unordered rows and without using ORDER BY the order is not guaranteed.
Using order by (select 1) make this a nondeterministic as we give all the rows the same ranking for the sort.
Here are two examples with exactly the same data but I insert the rows in different order => the result of this query is different for the two executions (since in small tables it usually fit the order we insert the rows), BUT THIS IS NOT GUARANTEED (link to the documentation below)
----------- Option one
DROP TABLE IF EXISTS #routes
CREATE TABLE #routes (src char(1), dest char(1))
go
INSERT #routes(src, dest)
VALUES('C', 'H'),
('H', 'C'),
('K', 'H'),
('H', 'K'),
('K', 'B')
go
select src source,dest destination from (
select ROW_NUMBER() over (partition by (IIF(src<dest,src,dest)+IIF(src<dest,dest,src)) order by (select 1)) rn,*
from #routes) a where rn=1
order by src
----------- Option two
DROP TABLE IF EXISTS #routes
CREATE TABLE #routes (src char(1), dest char(1))
go
INSERT #routes(src, dest)
VALUES('C', 'H'),
('H', 'C'),
('H', 'K'),
('K', 'H'),
('K', 'B')
go
select src source,dest destination from (
select ROW_NUMBER() over (partition by (IIF(src<dest,src,dest)+IIF(src<dest,dest,src)) order by (select 1)) rn,*
from #routes) a where rn=1
order by src
Same approach using CASE
SELECT DISTINCT
CASE WHEN src < dest THEN src ELSE dest END,
CASE WHEN src < dest THEN dest ELSE src END
FROM #routes