SELECT query based on mentioned result table

I have 3 tables.

TABLE1 with one column ROLL_NO

CREATE TABLE TABLE1 (ROLL_NO NUMBER(3) NOT NULL);

TABLE2 with 3 columns ROLL_NO, CLASS, SEC

CREATE TABLE TABLE2 (ROLL_NO NUMBER(3) NOT NULL, CLASS NUMBER(3) NOT NULL, SEC NUMBER(3) NOT NULL);

TABLE3 with 3 columns ROLL_NO, CODE, AMT

CREATE TABLE TABLE3 (ROLL_NO NUMBER(3) NOT NULL, CODE VARCHAR2(3) NOT NULL, AMT NUMBER(3) NOT NULL);

INSERT INTO TABLE1 VALUES (101);
INSERT INTO TABLE1 VALUES (102);
INSERT INTO TABLE1 VALUES (103);
INSERT INTO TABLE1 VALUES (104);
----------------------------------
INSERT INTO TABLE2 VALUES (101,1, 12);
INSERT INTO TABLE2 VALUES (102,1, 12);
INSERT INTO TABLE2 VALUES (103,1, 12);
INSERT INTO TABLE2 VALUES (104,1, 12);
--------------------------------------
INSERT INTO TABLE3 VALUES (101, 'A2', 100);
INSERT INTO TABLE3 VALUES (101, '10', 100);
INSERT INTO TABLE3 VALUES (102, 'B3', 200);
INSERT INTO TABLE3 VALUES (102, '10', 200);
INSERT INTO TABLE3 VALUES (103, '04', 300);

The SQL query which mentioned below:

SELECT T1.ROLL_NO, 
      T2.CLASS, 
      T2.SEC, 
      NVL(T3.CODE,0) AS CODE, 
      NVL(T3.AMT, 0) AS AMT
FROM TABLE1 T1
JOIN TABLE2 T2 ON T1.ROLL_NO = T2.ROLL_NO
LEFT JOIN TABLE3 T3 ON T1.ROLL_NO = T3.ROLL_NO
WHERE T1.ROLL_NO IN (101,102,103,104);

If we don't find any record i.e. CODE and AMT for particular ROLL_NO, by default we are assigning as 0.

The result for above query:

 ROLL_NO   CLASS   SEC    CODE    AMT
 -------------------------------------
  101       1      12     A2      100
  101       1      12     10      100
  102       1      12     B3      200
  102       1      12     10      200
  103       1      12      4      300
  104       1      12      0        0

I am looking for a query in such a way that

 a) if particular ROLL_NO has CODE 10 and also additional CODE values other than 10 then get that row which has CODE as 10 in the result table.
 b) if particular ROLL_NO don't have CODE 10 but has other additional CODE values then get that row in the result table.

In previous table, ROLL_NO 101 and 102 comes under case 'a' and 103, 104 comes under case 'b'

Final result should be

  ROLL_NO   CLASS   SEC    CODE    AMT
 -------------------------------------
  101       1      12     10      100
  102       1      12     10      200
  103       1      12      4      300
  104       1      12      0        0

I am looking for a query to get the above result but I am not able to get it. Till now, I tried using RANK function by partitioning on ROLL_NO and order by CODE in descending and select 1st row in each partition but it doesn't work if particular ROLL_NO have additional code greater than 10.

CodePudding user response：

You can use analytic function ROW_NUMBER() and it should work:

select roll_no, class, sec, code, amt
  from (SELECT T1.ROLL_NO, 
               T2.CLASS, 
               T2.SEC, 
               NVL(T3.CODE,0) AS CODE, 
               NVL(T3.AMT, 0) AS AMT,
     --analytic function 
               row_number() over (partition by t1.roll_no order by NVL(T3.CODE,0) desc) as row_num 
          FROM TABLE1 T1
          JOIN TABLE2 T2 ON T1.ROLL_NO = T2.ROLL_NO
          LEFT JOIN TABLE3 T3 ON T1.ROLL_NO = T3.ROLL_NO
         WHERE T1.ROLL_NO IN (101,102,103,104)) t
 where t.row_num = 1;

CodePudding user response：

You simply need a ROW_NUMBER() function to achieve your desired result -

SELECT T1.ROLL_NO, 
      T2.CLASS, 
      T2.SEC, 
      NVL(T3.CODE,0) AS CODE, 
      NVL(T3.AMT, 0) AS AMT
FROM TABLE1 T1
JOIN TABLE2 T2 ON T1.ROLL_NO = T2.ROLL_NO
LEFT JOIN (SELECT ROLL_NO, CODE, AMT,
                  ROW_NUMBER() OVER(PARTITION BY ROLL_NO ORDER BY CODE DESC) RN
                  -- If you have code greater than 10 also you have to use a CASE statement instead of simple order by clause
             FROM TABLE3) T3 ON T1.ROLL_NO = T3.ROLL_NO
                            AND RN = 1
WHERE T1.ROLL_NO IN (101,102,103,104);