Sql query

Copper Contributor

Hi Everyone,

I have one sql query to be solved as practice, have attached the ss

Output: need to select the src and dest that appears only ones

Delhi Mum

Delhi Kolkata

Mum Nagpur


I have solved it using lead function over 1 row

But if there are more such records not necessary to the very next row how should I query the output 

Please help






3 Replies
That's really to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.
Here is the script
Create table New_Airdata(src,dest)
Insert into New_Airdata(src,dest) values('Delhi','Mum');
Insert into New_Airdata(src,dest) values('Mum','Delhi');
Insert into New_Airdata(src,dest) values('Delhi','Kolkata');
Insert into New_Airdata(src,dest) values('Kolkata','Delhi');
Insert into New_Airdata(src,dest) values('Mum','Nagpur');

Src. Dest
Delhi. Mum
Delhi. Kolkata
Mum. Nagpur

I have tried using lead function, any other ways that we can query this?


Your table definition probably should specify that neither src nor dest can be NULL.


I have not tested it, but I think you want something like this:

SELECT T1.src, T1.dest
FROM New_Airdata AS T1
WHERE T1.src < T1.dest
    -- a row with swapped src and dest values
    (SELECT *
     FROM New_Airdata AS T2
     WHERE T2.src=T1.dest
       AND T2.dest = T1.src
--[possibly also sorting the results:]
ORDER BY T1.src, T1.dest


WHERE T1.src > T1.dest 

should give you the same routes, in reverse.

I'll assume you have no rows where src and dest have the same values - a nonsensical route.