Persistent Systems SQL Interview Question using LEAST(), GREATEST() and ROW_NUMBER() Functions 

Your way of solving problems is excellent.. I have seen different approaches to solve this problem however this one is the shortest and easiest to understand.. Thanks much!

select R1.origin , R1.destination from routes R1 left join routes R2 on R1.destination = R2.origin and r1.origin = r2.destination where (R2.origin is null or R2.destination is null) or r1.origin < r2.origin

with cte as( select origin,Destination,ROW_NUMBER()OVER() AS row_val FROM routes ),cte1 as( select origin as origin1,Destination as dist1 FROM routes ),cte2 as( select row_val,origin,Destination,origin1,dist1 FROM cte LEFT JOIN cte1 ON cte.Destination=cte1.origin1 ),cte3 as( select row_val,origin,Destination FROM cte2 where Destination=origin1 and origin=dist1 ),cte4 as( select origin,Destination FROM cte3 where row_val%2!=0 ORDER BY row_val ),cte5 as( select origin,Destination FROM cte2 where row_val NOT IN (select row_val FROM cte3) ) select * FROM cte4 union select * FROM cte5;

Superb explanation

Select Origin,Destination from (Select *,row_number() over (Partition by grp) as RN from (Select *,Case When Origin>Destination then concat(origin,Destination) Else concat(destination,origin) end as Grp from Routes)N) N where Rn =1;

In the Oracle Database.. We don't have that function ( least () , greatest() , what should I do now..?

SELECT ORIGIN,DESTINATION FROM (SELECT*,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RNUM FROM ROUTE) A WHERE RNUM NOT IN (2);

Great question , i solved and post daily sql question on linkdin and also tag you but you can't like or any thing, i think you are not active on linkdin

Here is my solution using CTE and LAG() function : WITH cte AS( SELECT *, LAG(Origin,1,0) OVER() AS prev_origin, LAG(Destination,1,0) OVER() AS prev_destination FROM routes) SELECT Origin, Destination FROM cte WHERE (Origin != prev_destination OR Destination != prev_origin);

---using row_number() analyticalfunction and inline view select * from (select r.*, row_number () over (partition by least(origin,destination) ,greatest(origin, destination) order by origin) rn from routes r) where rn=1; --using cte and row_number () with cte as (select r.*, row_number () over (partition by least(origin,destination) ,greatest(origin, destination) order by origin) rn from routes r) select origin , destination from cte where rn=1; ---other way using row_number and case statement with cte as (select r.*, row_number () over (partition by case when origin < destination then origin else destination end, case when origin > destination then origin else destination end order by origin) rn from routes r) select origin , destination from cte where rn=1;

Thanks For the Video

my solution for same problem with cte as( select t1.orgin,t1.destination,count(*) as cnt from travel t1 left join travel t2 on t1.orgin = t2.destination group by t1.orgin,t1.destination)select orgin,destination from cte where cnt