CODEWITHSUNDEEP
sqloracle-database
Dinushi Silva
Dinushi Silva

Reputation: 9

How to find a particular attribute value in comparing rows and columns

no tot place1   place2
1  1    colombo  negombo
1  2    negombo  kandy
1  3    kandy    colombo
2  1    colombo  kandy
2  2    kandy    jafna 
2  3    jafna    negombo
3  1    jafna    kaduwela
3  2    kaduwela jafna
4  1    trico    colombo

In this, I want to get no who is having the same place1 and place2.

Example:

no 1 has total 3 tot and its place1 and place2 is equal because it's 'Colombo' (comparing columns and row).

But no 2 doesn't have the same place1 and place2.

How to retrieve data like that with a query?

Upvotes: 0

Views: 136

Answers (3)

Bhargav Kothadia
Bhargav Kothadia

Reputation: 11

 SELECT A.*
  FROM A A
  WHERE NOT EXISTS (SELECT 1
                    FROM B B
                    WHERE A.A = B.A)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If I understand correctly, you want the lists in place1 to match the lists in place2 for each no.

If so, you can use listagg():

select no, listagg(place1, ',') within group (order by place1) as places
from t
group by no
having listagg(place1, ',') within group (order by place1) = listagg(place2, ',') within group (order by place2);

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

You can use ROW_NUMBER() to identify the terminal places of that journeys (or what they are). Order from start to destination and once vice versa. The terminals will have a ROW_NUMBER() of 1. Use PLACE1 for the terminals for the first order and PLACE2 for the second. Then join the results and compare the the terminals. If they are equal you got a round trip and can return the respective NO.

SELECT X.NO
       FROM (SELECT NO,
                    PLACE1 PLACE,
                    ROW_NUMBER() OVER (PARTITION BY NO
                                       ORDER BY TOTN ASC) RN
                    FROM ELBAT) X
            INNER JOIN (SELECT NO,
                               PLACE2 PLACE,
                               ROW_NUMBER() OVER (PARTITION BY NO
                                                  ORDER BY TOTN DESC) RN
                               FROM ELBAT) Y
                       ON Y.NO = X.NO
       WHERE X.RN = 1
             AND Y.RN = 1
             AND X.PLACE = Y.PLACE;

db<>fiddle

Upvotes: 0

Related Questions