This discussion is archived
4 Replies Latest reply: Feb 22, 2013 5:26 AM by 992747 RSS

Need to form SQL Query

992747 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points