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

 

Thanks 

 

 

 

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.
Hi,
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');

Output:
Src. Dest
Delhi. Mum
Delhi. Kolkata
Mum. Nagpur

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

@Neha11 

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
   OR NOT EXISTS
    -- 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

Alternatively,

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.