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

[@Happusingh2]

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!

[@CloudChallengers]

@gouravraj1169, Thanks for the feedback 🙂. Please watch other SQL videos as well that are posted on our channel.

[@Happusingh2]

@@CloudChallengers I have already watched most of the videos posted.. Keep those coming.. Kudos!

[@snehsparsh7954]

Six ways to solve above question: -- Approach 1: -- This query placing the smaller value as 'Origin' and the larger as 'Destination'. -- The DISTINCT keyword ensures that duplicate routes (e.g., A-B and B-A) are removed. SELECT DISTINCT CASE WHEN Origin < Destination THEN Origin ELSE Destination END AS Origin, CASE WHEN Origin > Destination THEN Origin ELSE Destination END AS Destination FROM practiceDB1.routes_tbl; -- Approach 2: -- This query identifies unique routes by using a LEFT JOIN on the same table, -- where each route is checked against its reverse pair (i.e., A-B and B-A). -- If a matching reverse route is found, the query filters out duplicate entries. -- The WHERE clause ensures that only one direction of each route is included. SELECT r1.Origin, r1.Destination FROM practiceDB1.routes_tbl r1 LEFT JOIN practiceDB1.routes_tbl 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; -- Approach 3: -- This query is using LEAST and GREATEST functions to always assign the smaller value as 'Origin' -- and the larger value as 'Destination'. -- The DISTINCT keyword ensures that duplicate routes (e.g., A-B and B-A) are removed. SELECT DISTINCT LEAST(Origin, Destination) AS Origin, GREATEST(Origin, Destination) AS Destination FROM practiceDB1.routes_tbl; -- Approach 4: -- This query uses a CTE to assign a row number to each route -- after partitioning by the standardized representation (LEAST and GREATEST functions). -- It then filters to retain only the first occurrence, ensuring unique route representation. WITH route_stats AS ( SELECT Origin, Destination, ROW_NUMBER() OVER (PARTITION BY LEAST(Origin, Destination), GREATEST(Origin, Destination) ORDER BY Origin) AS r_num FROM practiceDB1.routes_tbl ) SELECT Origin, Destination FROM route_stats WHERE r_num = 1; --Approach 5: Self-Join with a Filtering Condition --This approach ensures that only one direction of each route is retained by performing a self-join and filtering out duplicate entries. SELECT r1.Origin, r1.Destination FROM practiceDB1.routes_tbl r1 WHERE NOT EXISTS ( SELECT 1 FROM practiceDB1.routes_tbl r2 WHERE r1.Origin = r2.Destination AND r1.Destination = r2.Origin AND r1.Origin > r1.Destination ); --Approach 6: Using GROUP BY --This approach enforce a consistent ordering is to use GROUP BY with the MIN and MAX functions. SELECT MIN(Origin) AS Origin, MAX(Origin) AS Destination FROM practiceDB1.routes_tbl GROUP BY LEAST(Origin, Destination), GREATEST(Origin, Destination);

[@sravankumar1767]

Superb explanation

[@akash.i7391]

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

[@CloudChallengers]

least (), greatest() functions should work in oracle. Please check your query

[@landchennai8549]

select distinct case when Origin < Destination then Origin else destination end as Origin , case when Origin > Destination then Origin else destination end as destination from #routes

[@mohanprasanthmanickam8292]

Thanks For the Video

[@ChaitanyaKariya-x4q]

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

[@hairavyadav6579]

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

[@CloudChallengers]

@hairavyadav6579, Due to some personal commitments I'm not much active on LinkedIn these days. I will start responding soon.

[@HARSHRAJ-gp6ve]

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;

[@Jhanavi.Sunilkongadi]

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

[@pritambanerjee6957]

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);

[@monasanthosh9208]

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;

[@Harish0402]

---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;

[@vivektiwari372]

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

[@CloudChallengers]

@vivektiwari372, Thanks for sharing different approach.