13 Replies Latest reply: Mar 7, 2013 1:35 PM by Gaff RSS

    Need help in framing an SQL query

    966025
      Hi ,

      Requirement is that

      Let’s say I have a table say X


      I am passing two input params from UI and in DAO i am querying these input prams against the table X(has c1,c2 as columns ) (as it has two columns mapping the input params A,B from UI).

      if either one or Both of the input params matches ,only then i should fetch the records accordingly. It's understood that there won't be a scenario that both input params are null from UI.

      I have three conditions here,

      1.     A match against X, B doesnt Match
      2.     B match against X, A doesnt Match
      3.     Both A & B match against X


      if i use let's say,

      SELECT *FROM X WHERE (c1= A OR c2= B) .

      •     if A match against X B doesnt Match It is fetching all the records matching A - requirement fullfilled
      •     B match against X A doesnt Match It is fetching all the records matching B- requirement fullfilled.
      •     Both A & B match against X It is fetching all the records from the table matching the first condition (which here is A) . requirement not fullfilled.


      I am thinking of using the following query but I need to frame it in a way that based on ACHK & BCHK we need to add the conditions.

      SELECT CASE WHEN C1 = A THEN 0 ELSE 9 END AS ACHK, CASE WHEN C2 = B THEN 0 ELSE 9 END AS BCHK FROM X

      Based on ACHK & BCHK append the conditions. i.e.

      IF ACHK = 0 AND BCHK = 0 THEN
      AND C1 = A AND C2 = B
      ELSE
      AND (C1 = A OR C2 = B)
      Please help.
        • 1. Re: Need help in framing an SQL query
          Solomon Yakobson
          It is not clear what query should return. If table X has:
          C1 C2
          -- --
          A  1
          2  B
          A  B
          Should it return all 3 rows or just the third one. If all 3, then
          SELECT *FROM X WHERE c1 = A OR c2 = B;
          is all you need. If just the third one, then something like:
          SELECT  *
            FROM  (
                   SELECT  X.*,
                           CASE
                             WHEN c1 = A AND c2 = B THEN 2
                             WHEN c1 = A OR c2= B THEN 1
                             ELSE 0
                           END weight,
                           MAX(
                               CASE
                                 WHEN c1 = A AND c2 = B THEN 2
                                 WHEN c1= A OR c2= B THEN 1
                                 ELSE 0
                               END
                              ) OVER() max_weight
                     FROM  X
                  )
            where weight = max_weight
          /
          SY.
          • 2. Re: Need help in framing an SQL query
            Gaff
            If I understand your post, something is amiss. Are these results below not as you would expect? What are you doing differently?
            create table X (
                c1  varchar2(20),
                c2  number
            );
            
            
            insert into X values ('ABC',1);
            insert into X values ('ABC',2);
            insert into X values ('ABC',3);
            insert into X values ('ABC',4);
            insert into X values ('ABC',5);
            insert into X values ('ABC',6);
            insert into X values ('ABC',7);
            insert into X values ('ABC',8);
            insert into X values ('ABC',9);
            insert into X values ('ABC',10);
            insert into X values ('ZZZ',1);
            insert into X values ('ZZZ',2);
            insert into X values ('ZZZ',3);
            insert into X values ('ZZZ',10);
            insert into X values ('ZZZ',20);
            insert into X values ('ZZZ',30);
            
            select * 
            from x 
            where (c1='ABC' or c2=10)
            order by c1
            
            
            
            C1     C2
            'ABC'     1
            'ABC'     2
            'ABC'     3
            'ABC'     4
            'ABC'     5
            'ABC'     6
            'ABC'     7
            'ABC'     8
            'ABC'     9
            'ABC'     10
            'ZZZ'     10
            • 3. Re: Need help in framing an SQL query
              Bill Haverberg
              In this case you'll want to break it into two different queries, one where A matches X (on C1) and the other where B matches X (on C2). Join the two queries with a UNION clause.

              If I understand right - its not very clear - I think the problem you're describing (where both match, but you're only getting the "A" results) might be a short-circuit in your query logic that is causing the right hand expression not to be evaluated. Using the UNION above will simplify the logic and avoid the short-circuit (if that is the problem).

              Edited by: Bill Haverberg on Mar 5, 2013 12:03 PM
              • 4. Re: Need help in framing an SQL query
                966025
                Hi All ,
                Thanks for all your replies.

                Sorry that my post is not clear.

                Here is what precisely i required.

                We have a UI which has two components where user selects some data (atleast one component will be selected)
                and this data when is reached to DAO i need to frame a query in such a way that ,

                if either one or both the inputs are matched ,records should be fetched accordingly.

                For E.g:
                INSERT INTO X VALUES('13' ,'26')
                INSERT INTO X VALUES('13' ,'24')

                1 rows inserted.
                1 rows inserted.

                In DAO ,currently i have used the following query (obvious it is failing and i am in hunt of a query which should match my requirement)

                SELECT C1,C2 FROM X WHERE (C1 = '13'OR C2 ='26' ) (here 13 ,26 are the values coming from UI which user has selected)
                ----------------------------
                13     24
                13     26

                In the above case, user has selected values from BOTH the components hence result is only one record which matches c1 = 13 and c2 =26 but i am getting two records.

                Hence i thought i needed to know first if there is a MATCH on C1 (with 13) and MATCH on C2 (with 26)

                After which i can frame the query, so i decided to use CASE command in where clause but after riddling my ahead for like hours i could only stuck up at the following query

                SELECT CASE WHEN C1 = '13' THEN 0 ELSE 9 AS ACHK, CASE WHEN C2 = '26' THEN 0 ELSE 9 END AS BCHK FROM X.
                ----------------------
                ACHK BCHK
                0     9
                0     0

                0 - Match found
                9- Match not found

                as mentioned by Bill Haverberg_

                I have used the UNION operator but no luck.

                SELECT C1 ,C2 FROM X WHERE C1 = '13'
                UNION
                SELECT C1,C2 FROM X WHERE C2 = '26'
                ---------------------------
                13     24
                13     26


                Thanks.
                • 5. Re: Need help in framing an SQL query
                  Solomon Yakobson
                  963022 wrote:

                  In the above case, user has selected values from BOTH the components hence result is only one record which matches c1 = 13 and c2 =26 but i am getting two records.
                  Use:
                  SELECT  *
                    FROM  X
                    WHERE (
                              c1 = A
                           OR
                              A IS NULL
                          )
                      AND (
                              c2 = B
                           OR
                              B IS NULL
                          )
                  /
                  For example:
                  SQL> create table x(
                    2                 c1 number,
                    3                 c2 number
                    4                )
                    5  /
                  
                  Table created.
                  
                  SQL> INSERT INTO X VALUES(13,26)
                    2  /
                  
                  1 row created.
                  
                  SQL> INSERT INTO X VALUES(13,24)
                    2  /
                  
                  1 row created.
                  
                  SQL> commit
                    2  /
                  
                  Commit complete.
                  
                  SQL> select  *
                    2    from  x
                    3  /
                  
                          C1         C2
                  ---------- ----------
                          13         26
                          13         24
                  
                  SQL> variable a number
                  SQL> variable b number
                  SQL> exec :a := 13;
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> exec :b := 26;
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> SELECT  *
                    2    FROM  X
                    3    WHERE (
                    4              c1 = :A
                    5           OR
                    6              :A IS NULL
                    7          )
                    8      AND (
                    9              c2 = :B
                   10           OR
                   11              :B IS NULL
                   12          )
                   13  /
                  
                          C1         C2
                  ---------- ----------
                          13         26
                  
                  SQL> exec :b := null;
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> SELECT  *
                    2    FROM  X
                    3    WHERE (
                    4              c1 = :A
                    5           OR
                    6              :A IS NULL
                    7          )
                    8      AND (
                    9              c2 = :B
                   10           OR
                   11              :B IS NULL
                   12          )
                   13  /
                  
                          C1         C2
                  ---------- ----------
                          13         26
                          13         24
                  
                  SQL> exec :a := null;
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> exec :b := 26;
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> SELECT  *
                    2    FROM  X
                    3    WHERE (
                    4              c1 = :A
                    5           OR
                    6              :A IS NULL
                    7          )
                    8      AND (
                    9              c2 = :B
                   10           OR
                   11              :B IS NULL
                   12          )
                   13  /
                  
                          C1         C2
                  ---------- ----------
                          13         26
                  
                  SQL> exec :a := null;
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> exec :b := null;
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> SELECT  *
                    2    FROM  X
                    3    WHERE (
                    4              c1 = :A
                    5           OR
                    6              :A IS NULL
                    7          )
                    8      AND (
                    9              c2 = :B
                   10           OR
                   11              :B IS NULL
                   12          )
                   13  /
                  
                          C1         C2
                  ---------- ----------
                          13         26
                          13         24
                  
                  SQL>
                  SY.
                  • 6. Re: Need help in framing an SQL query
                    Solomon Yakobson
                    And if you don't want to return any rows when both A & B are null:
                    SELECT  *
                      FROM  X
                      WHERE (
                                c1 = A
                             OR
                                A IS NULL
                            )
                        AND (
                                c2 = B
                             OR
                                B IS NULL
                            )
                        AND COALESCE(A,B) IS NOT NULL
                    /
                    For example:
                    SQL> variable a number
                    SQL> variable b number
                    SQL> exec :a := 13;
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> exec :b := 26;
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> SELECT  *
                      2    FROM  X
                      3    WHERE (
                      4              c1 = :A
                      5           OR
                      6              :A IS NULL
                      7          )
                      8      AND (
                      9              c2 = :B
                     10           OR
                     11              :B IS NULL
                     12          )
                     13      AND COALESCE(:a,:b) IS NOT NULL
                     14  /
                    
                            C1         C2
                    ---------- ----------
                            13         26
                    
                    SQL> exec :b := null;
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> SELECT  *
                      2    FROM  X
                      3    WHERE (
                      4              c1 = :A
                      5           OR
                      6              :A IS NULL
                      7          )
                      8      AND (
                      9              c2 = :B
                     10           OR
                     11              :B IS NULL
                     12          )
                     13      AND COALESCE(:a,:b) IS NOT NULL
                     14  /
                    
                            C1         C2
                    ---------- ----------
                            13         26
                            13         24
                    
                    SQL> exec :a := null;
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> exec :b := 26;
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> SELECT  *
                      2    FROM  X
                      3    WHERE (
                      4              c1 = :A
                      5           OR
                      6              :A IS NULL
                      7          )
                      8      AND (
                      9              c2 = :B
                     10           OR
                     11              :B IS NULL
                     12          )
                     13      AND COALESCE(:a,:b) IS NOT NULL
                     14  /
                    
                            C1         C2
                    ---------- ----------
                            13         26
                    
                    SQL> exec :a := null;
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> exec :b := null;
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> SELECT  *
                      2    FROM  X
                      3    WHERE (
                      4              c1 = :A
                      5           OR
                      6              :A IS NULL
                      7          )
                      8      AND (
                      9              c2 = :B
                     10           OR
                     11              :B IS NULL
                     12          )
                     13      AND COALESCE(:a,:b) IS NOT NULL
                     14  /
                    
                    no rows selected
                    
                    SQL>
                    However, keep in mind, creating "one size fits all" query could result in sub-optimal performance. For example, if columns(s) are indexed, "one size fits all" query will do full scan while tailored query will use index:
                    SQL> create index x_idx1 on x(c1);
                    
                    Index created.
                    
                    SQL> variable a number
                    SQL> variable b number
                    SQL> exec :a := 13
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> exec :b := null
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> explain plan for
                      2  select  *
                      3    from  x
                      4    where c1 = :a
                      5  /
                    
                    Explained.
                    
                    SQL> @?\rdbms\admin\utlxpls
                    
                    PLAN_TABLE_OUTPUT
                    ---------------------------------------------------------------------------------------------------------------
                    
                    Plan hash value: 2924283013
                    
                    --------------------------------------------------------------------------------------
                    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
                    --------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT            |        |     1 |    26 |     2   (0)| 00:00:01 |
                    |   1 |  TABLE ACCESS BY INDEX ROWID| X      |     1 |    26 |     2   (0)| 00:00:01 |
                    |*  2 |   INDEX RANGE SCAN          | X_IDX1 |     1 |       |     1   (0)| 00:00:01 |
                    --------------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    
                    PLAN_TABLE_OUTPUT
                    ---------------------------------------------------------------------------------------------------------------
                    
                    ---------------------------------------------------
                    
                       2 - access("C1"=TO_NUMBER(:A))
                    
                    Note
                    -----
                       - dynamic sampling used for this statement (level=2)
                    
                    18 rows selected.
                    
                    SQL> explain plan for
                      2  SELECT  *
                      3    FROM  X
                      4    WHERE (
                      5              c1 = :A
                      6           OR
                      7              :A IS NULL
                      8          )
                      9      AND (
                     10              c2 = :B
                     11           OR
                     12              :B IS NULL
                     13          )
                     14      AND COALESCE(:a,:b) IS NOT NULL
                     15  /
                    
                    Explained.
                    
                    SQL> @?\rdbms\admin\utlxpls
                    
                    PLAN_TABLE_OUTPUT
                    ---------------------------------------------------------------------------------------------------------------
                    
                    Plan hash value: 2729574260
                    
                    ---------------------------------------------------------------------------
                    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                    ---------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT   |      |     1 |    26 |     3   (0)| 00:00:01 |
                    |*  1 |  FILTER            |      |       |       |            |          |
                    |*  2 |   TABLE ACCESS FULL| X    |     1 |    26 |     3   (0)| 00:00:01 |
                    ---------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    
                    PLAN_TABLE_OUTPUT
                    ---------------------------------------------------------------------------------------------------------------
                    
                    ---------------------------------------------------
                    
                       1 - filter(COALESCE(:A,:B) IS NOT NULL)
                       2 - filter((:A IS NULL OR "C1"=TO_NUMBER(:A)) AND (:B IS NULL OR
                                  "C2"=TO_NUMBER(:B)))
                    
                    Note
                    -----
                       - dynamic sampling used for this statement (level=2)
                    
                    20 rows selected.
                    
                    SQL>
                    SY.
                    • 7. Re: Need help in framing an SQL query
                      Frank Kulash
                      Hi,

                      If I understand you, this does what you want:
                      WITH     targets          AS
                      (
                           SELECT     'ABC' AS c1, 10 AS c2  FROM dual     UNION ALL
                           SELECT     'ABC',          20            FROM dual     UNION ALL
                           SELECT     'FOO',          10            FROM dual     UNION ALL
                           SELECT     'FOO',          20            FROM dual     UNION ALL
                           SELECT     'FOO',          99            FROM dual
                      )
                      ,     got_rnk          AS
                      (
                           SELECT     x.c1, x.c2
                           ,     t.c1          AS target_c1
                           ,     t.c2          AS target_c2
                           ,     DENSE_RANK () OVER ( PARTITION BY  t.c1, t.c2
                                                     ORDER BY          CASE
                                                                   WHEN  x.c1  = t.c1
                                                           THEN  1
                                                           ELSE  0
                                                               END
                                                     +  CASE
                                                                   WHEN  x.c2  = t.c2
                                                           THEN  2
                                                           ELSE  0
                                                               END
                                               DESC
                                             )          AS rnk
                           FROM     targets  t
                           JOIN           x  ON   x.c1     = t.c1
                                         OR   x.c2     = t.c2
                      )
                      SELECT       c1, c2
                      ,       target_c1, target_c2
                      FROM       got_rnk
                      WHERE       rnk     = 1
                      ;
                      This allows you to match with any number of parameter pairs (including 1) at the same time. I used 5 pairs above, and got these results, using the sample data that Gaff posted:
                      C1                 C2 TAR  TARGET_C2
                      ---------- ---------- --- ----------
                      ABC                10 ABC         10
                      ZZZ                20 ABC         20
                      ABC                10 FOO         10
                      ZZZ                10 FOO         10
                      ZZZ                20 FOO         20
                      This is looking for the best match. What does the mean?
                      (1) Matching both parameters, if possible (like 'ABC', 10, above). If there is no match for both, then
                      (2) matching just c2. (E.g., when I searched for 'ABC', 20, the best it could do was 'ZZZ', 20. There were lots of matches on c1 alone, but I ignored them because the match on c2 alone is better.) If there is still no match, then
                      (3) matching just c1.

                      When I searched for 'FOO', 99, nothing came close, so no results were produced.

                      In case of a tie, all contenders are returned. (E.g., when I searched for 'FOO', 10, then both 'ABC', 10 and 'ZZZ', 10 were equally good matches.) If you want only one of them, then use ROW_NUMBER instead of DENSE_RANK.

                      In practice, you may only want to look for one target pair at a time. In that case, you can remove the targets sub-query and the join, and use the parameters directly in got_rnk. You can hard-code the parameters, or pass them as bind variables.
                      • 8. Re: Need help in framing an SQL query
                        Gaff
                        Frank:

                        Where is X aliased? I would think it would an alias for Targets or something.

                        Gaff
                        • 9. Re: Need help in framing an SQL query
                          Bill Haverberg
                          Ah, I see now. If both elements from the interface exist than you want only those records which match both, but if only one exists you want those records which match on either column.

                          I haven't tested this in Oracle - the concept is good but you might have to tweak any typos, and play around with how this pulls together depending on how you implement this (PL/SQL cursors vs SQL statement with the UI_SELECTED function iembedded, handle the bind variables appropriately, etc).

                          You could create a value keyed to the presence or absence of an interface column. Here's a query fragment for that:

                          CREATE OR REPLACE FUNCTION UI_SELECTED(UI_A IN NUMBER, UI_B NUMBER) RETURN NUMBER IS
                          UI_X number;
                          BEGIN
                          UI_X = sign(nvl(A,0)) + sign(nvl(B,0))*2;
                          return UI_X;
                          END;

                          So ... IF
                          A is not selected and B is selected, return 2
                          A is selected and B is not selected, return 1
                          A is selected and B is selected, return 3

                          Now you can use this in a statement:

                          Select * from X where UI_SELECTED(:A, :B) == 1 and c1 = :A
                          UNION
                          Select * from X where UI_SELECTED(:A,:B) == 2 and cc2 = :B
                          UNION
                          Select * from X where UI_SELECTED(:A,:B) == 3 and c1 = :A and C2 = :B

                          With the above query, only one of the selects will have data (depending on the value returned from UI_SELECTED). Putting them all in a UNION is a simple workaround to avoid having to use CASE or DECODE.
                          • 10. Re: Need help in framing an SQL query
                            Frank Kulash
                            Hi, Gaff,
                            Gaff wrote:
                            Where is X aliased? I would think it would an alias for Targets or something.
                            X is not aliased, nor is x an alias for something else. X is the actual, full table name.
                            • 11. Re: Need help in framing an SQL query
                              966025
                              Hi Frank Kulash,
                              The code snippet has WORKED like a GEM ,although i have to educate a LOT on analytic functions to thoroughly digest your query but it has saved me my life :)
                              Would be glad if you can shed some light on role of DENSE_RANK here (ofcourse i did read the forums but i am confused on what basis a row is ranked).

                              Thanks
                              • 12. Re: Need help in framing an SQL query
                                Frank Kulash
                                Hi,
                                963022 wrote:
                                Would be glad if you can shed some light on role of DENSE_RANK here (ofcourse i did read the forums but i am confused on what basis a row is ranked).
                                Complicated queries and expressions are usually built of smaller, simpler parts. To understand how the big, complicated thing works, look first at the smaller, simpler components of it, and make sure you understand how they work.

                                In this case, the query is composed of a sub-query and a main query. Start by looking at the sub-query only. Display all rows and all columns of the sub-query.

                                Break apart the complicated components of that sub-query. Rnk depends on an ORDER BY clause, but the expression used in that ORDER BY clause isn't displayed anywhere. Copy and display just that expression. (I called this case_1_2 in the query below.) Even that may be too complicated to understand, so break it apart. Case_1_2 is formed by adding 2 smaller CASE expressions; in the query below, I displayed each of the in separate columns (case_1 and case_2).

                                So here's a query you might use to understand and debug the query I posted:
                                WITH     targets          AS
                                (
                                     SELECT     'ABC' AS c1, 10 AS c2  FROM dual     UNION ALL
                                     SELECT     'ABC',          20            FROM dual     UNION ALL
                                     SELECT     'FOO',          10            FROM dual     UNION ALL
                                     SELECT     'FOO',          20            FROM dual     UNION ALL
                                     SELECT     'FOO',          99            FROM dual
                                )
                                ,     got_rnk          AS
                                (
                                     SELECT     x.c1, x.c2
                                     ,     t.c1          AS target_c1
                                     ,     t.c2          AS target_c2
                                     ,     DENSE_RANK () OVER ( PARTITION BY  t.c1, t.c2
                                                               ORDER BY          CASE
                                                                             WHEN  x.c1  = t.c1
                                                                     THEN  1
                                                                     ELSE  0
                                                                         END
                                                               + CASE
                                                                             WHEN  x.c2  = t.c2
                                                                     THEN  2
                                                                     ELSE  0
                                                                         END
                                                         DESC
                                                       )          AS rnk
                                --     *****  DEBUGGING SECTION  *****     The following columns are included only 
                                --                                       to see how the function above works.
                                     ,                               CASE
                                                                             WHEN  x.c1  = t.c1
                                                                     THEN  1
                                                                     ELSE  0
                                                                         END
                                                               + CASE
                                                                             WHEN  x.c2  = t.c2
                                                                     THEN  2
                                                                     ELSE  0
                                                                         END           AS case_1_2
                                     ,                               CASE
                                                                             WHEN  x.c1  = t.c1
                                                                     THEN  1
                                                                     ELSE  0
                                                                         END           AS case_1
                                     ,                            CASE
                                                                             WHEN  x.c2  = t.c2
                                                                     THEN  2
                                                                     ELSE  0
                                                                         END           AS case_2
                                --     *****  END OF DEBUGGING SECTION  *****
                                     FROM     targets  t
                                     JOIN           x  ON   x.c1     = t.c1
                                                   OR   x.c2     = t.c2
                                )
                                --     The following main query is only for debugging
                                SELECT       *
                                FROM       got_rnk
                                ORDER BY  target_c1, target_c2
                                ,            rnk
                                ;
                                Output:
                                              TARGET TARGET          CASE
                                C1         C2 _C1       _C2    RNK   _1_2 CASE_1 CASE_2
                                ------ ------ ------ ------ ------ ------ ------ ------
                                ABC        10 ABC        10      1      3      1      2
                                ZZZ        10 ABC        10      2      2      0      2
                                ABC         5 ABC        10      3      1      1      0
                                ABC         6 ABC        10      3      1      1      0
                                ABC         7 ABC        10      3      1      1      0
                                ABC         1 ABC        10      3      1      1      0
                                ABC         3 ABC        10      3      1      1      0
                                ABC         2 ABC        10      3      1      1      0
                                ABC         8 ABC        10      3      1      1      0
                                ABC         9 ABC        10      3      1      1      0
                                ABC         4 ABC        10      3      1      1      0
                                
                                ZZZ        20 ABC        20      1      2      0      2
                                ABC        10 ABC        20      2      1      1      0
                                ABC         2 ABC        20      2      1      1      0
                                ABC         1 ABC        20      2      1      1      0
                                ABC         9 ABC        20      2      1      1      0
                                ABC         8 ABC        20      2      1      1      0
                                ABC         7 ABC        20      2      1      1      0
                                ABC         6 ABC        20      2      1      1      0
                                ABC         5 ABC        20      2      1      1      0
                                ABC         4 ABC        20      2      1      1      0
                                ABC         3 ABC        20      2      1      1      0
                                
                                ABC        10 FOO        10      1      2      0      2
                                ZZZ        10 FOO        10      1      2      0      2
                                
                                ZZZ        20 FOO        20      1      2      0      2
                                Study this output to see how the different components work.
                                For example, the first 12 rows of output show what we found when we were looking for ('ABC', 10). There were 12 rows in table x that had either ca='ABC' or c2=10 (or both). We used the rnk column to find the best match: lower numbers mean better matches. How did we do that? By using the DENSE_RANK function with a DESCending order by clause, that is, we used <tt> DENSE_RANK () OVER (... ORDER BY exp DESC) </tt>, which means that higher values of exp produce lower values from DENSE_RANK. What is exp? It's the column I called case_1_2. As you can see, when there was a perfect match ('ABC', 10), then case_1_2 had the value 3. Less perfect matches got lower values of case_1_2; for example ('ZZZ', 10) only had 2 in the case_1_2 column, and ('ABC', 5) only had 1. Why did case_1_2 have values of 3, 2 or 1 on those rows? Well, it's the sum of case_1 and case_2, so look at those individual columns, and see how they are computed.
                                • 13. Re: Need help in framing an SQL query
                                  Gaff
                                  So you used the table X as the OP created it? I guess I'm confused as to what Targets is then. I was assuming that was your recreation of X for your example.