Forum Discussion

Neha11's avatar
Neha11
Copper Contributor
Feb 05, 2023

Sql query

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 

 

 

 

sql query
  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Feb 06, 2023
    That's really to less on information.
    Please post table design as DDL, some sample data as DML statement and the expected result.
    • Neha11's avatar
      Neha11
      Copper Contributor
      Feb 16, 2023
      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?
      • SnowMan55's avatar
        SnowMan55
        Bronze Contributor
        Feb 17, 2023

        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.

         

         

Share

Resources