4 Replies Latest reply: Feb 22, 2013 7:26 AM by 992747 RSS

    Need to form SQL Query

    992747
      Hi friends,

      I have two tables

      TABLE1 contains 2 columns kpi_id and kpi_code

      Data in table1 will something as follows:

      SET_ID - KPI_ID - KPI_CODE

      1 - 1 - KKK
      1 - 2 - GGG
      1 - 3 - HHH


      TABLE2 contains 2 columns kpi_id and PRIORITY

      Data in table 2 will be something as follows:

      KPI_ID - PRIORITY
      4 - 1
      6 - 2
      5 - 3
      2 - 4
      1 - 5
      3 - 6

      ------------------------------

      Now I need to read data from table1 with the priority in the table 2 which should be best available priority.

      i.e. In table1, for the set_id 1 the available KPI_IDs are 1, 2 and 3 only.

      But in table 2, the priority 1 is 4 and which is not there in table1.. same case for priority 2 and 3. And priority 4 is KPI_ID 2. and which exists in table1.

      So I need to read table1.KPI_CODE with KPI_ID 2.

      Please provide the info on how to write this query..

      Thanks in advance

      - HKishore

      Edited by: 989744 on Feb 22, 2013 4:18 AM

      Edited by: 989744 on Feb 22, 2013 4:27 AM

      Edited by: 989744 on Feb 22, 2013 4:47 AM
        • 1. Re: Need to form SQL Query
          Karthick_Arp
          Welcome to the forum!!

          Looking for this
          SQL> with t1
            2  as
            3  (
            4  select 1 set_id, 1 kpi_id, 'KKK' kpi_code from dual union all
            5  select 1 set_id, 2 kpi_id, 'GGG' kpi_code from dual union all
            6  select 1 set_id, 3 kpi_id, 'HHH' kpi_code from dual
            7  )
            8  , t2
            9  as
           10  (
           11  select 4 kpi_id, 1 priority from dual union all
           12  select 6 kpi_id, 2 priority from dual union all
           13  select 5 kpi_id, 3 priority from dual union all
           14  select 2 kpi_id, 4 priority from dual union all
           15  select 1 kpi_id, 5 priority from dual union all
           16  select 2 kpi_id, 6 priority from dual
           17  )
           18  select set_id, kpi_code
           19    from (
           20            select t1.set_id, t1.kpi_code, rank() over(partition by t1.set_id order by t2.priority) rk
           21              from t1
           22              left join t2
           23                on t1.kpi_id = t2.kpi_id
           24         )
           25   where rk = 1
           26  /
           
              SET_ID KPI
          ---------- ---
                   1 GGG
           
          • 2. Re: Need to form SQL Query
            992747
            Hi Karthik,

            thanks for your reply.

            I can't hard code the values. Because the data in these both tables will grow like any thing. Is this query not possible without "with clause" ?

            Thanks in advance.
            -HKishore
            • 3. Re: Need to form SQL Query
              NextName
              Hello HKishore

              Karthik uses the WITH clause because it's easier on the fly (and because he hasn't got your tables).

              AFAICT, you may simply use the select statement without the WITH-part and translate t1 into table1, t2 into table2 (or whatever your real table names are).

              HTH
              NN

              Edited by: NextName on Feb 27, 2013 10:21 AM
              • 4. Re: Need to form SQL Query
                992747
                Sorry I didn't understand the query.. thank you karthik. I am able to get expected result.

                Edited by: 989744 on Feb 22, 2013 5:26 AM