1 2 Previous Next 20 Replies Latest reply: Nov 20, 2013 9:09 AM by BluShadow

# SQL Puzzler: Line Up Column-Wise Rankings

We have a table that looks like the following:

COL_1_RANKCOL_1_VALUECOL_2_RANKCOL_2_VALUE
1AAA3HHH
2BBB2GGG
3CCC5JJJ
4DDD1FFF
5EEE4III

We want to extract a *single* row for each case where the COL_1_RANK and COL_2_RANK are equivalent. We then want the values that correspond to this mutual rank to be displayed in this same row. So, the desired output would look like this:

MUTUAL_RANKCOL_1_VALUECOL_2_VALUE
1AAAFFF
2BBBGGG
3CCCHHH
4DDDIII
5EEEJJJ

The other requirement is that we want to do this in a *single table pass*. So, we are aware of solutions using two different WITH clauses and then joining on the ranks. That's not what we are looking for here.

We are suspecting that some analytic function could be used here but, just can't seem to ferret it out. Basically, for each row, take the value of COL_1_RANK and, then, over the set of all COL_2_RANK values, find the row where COL_2_RANK =s COL_1_RANK. Pull the COL_2_VALUE from this row.

Any SQL Smarties out there that have a solution for doing this?

Thanks,

-Joe

• ###### 1. Re: SQL Puzzler: Line Up Column-Wise Rankings

Can you do a self join to accomplish this, i.e.:

SELECT

A.COL_1_RANK as MUTUAL_RANK,

A.COL_1_VALUE,

B.COL_2_VALUE

FROM

(SELECT COL_1_RANK, COL_1_VALUE FROM TABLE) A,

(SELECT COL_2_RANK, COL_2_VALUE FROM TABLE) B

WHERE A.COL_1_RANK = B.COL_2_RANK;

• ###### 2. Re: SQL Puzzler: Line Up Column-Wise Rankings

No, Andy...that makes two passes over the table. The puzzler is how to do it in a single pass.

BTW, I have just solved this and will post the answer here in a bit but, thought people might find it fun to try to figure this out before I do. As a hint, I used LISTAGG.

-Joe

• ###### 3. Re: SQL Puzzler: Line Up Column-Wise Rankings

perhaps something like this?

drop table t;

create table t (

col_1_rank number

, col_1_value varchar2(20)

, col_2_rank number

, col_2_value varchar2(20)

);

insert into t values (1, 'AAA', 3, 'HHH');

insert into t values (2, 'BBB', 2, 'GGG');

insert into t values (3, 'CCC', 5, 'JJJ');

insert into t values (4, 'DDD', 1, 'FFF');

insert into t values (5, 'EEE', 4, 'III');

with

basedata as (

select col_1_rank

, col_1_value

, ',' || listagg(col_2_value, ',') within group (order by col_2_rank) over() || ',' col_2_value

from t

)

select col_1_rank

, col_1_value

, substr(col_2_value, instr(col_2_value, ',', 1, col_1_rank) + 1, (instr(col_2_value, ',', 1, col_1_rank + 1) - 1) - (instr(col_2_value, ',', 1, col_1_rank))) col_2_value

from basedata

order by col_1_rank;

COL_1_RANK COL_1_VALUE          COL_2_VALUE

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

1 AAA                  FFF

2 BBB                  GGG

3 CCC                  HHH

4 DDD                  III

5 EEE                  JJJ

My contribution for the obfuscation contest...

But I am not sure if the WINDOW SORT operation will indeed be cheaper than a join (if this was the idea).

Regards

Martin

• ###### 4. Re: SQL Puzzler: Line Up Column-Wise Rankings

Hi, Joe,

You can do that using UNPIVOT and PIVOT, like this:

SELECT   *

FROM      table_x

UNPIVOT   (               (mutual_rank, val)

FOR  label IN ( (col_1_rank,  col_1_value)  AS 1

, (col_2_rank,  col_2_value)  AS 2

)

)

PIVOT     (    MIN (val)

FOR  label  IN ( 1  AS col_1_value

, 2  AS col_2_vlaue

)

)

ORDER BY  mutual_rank

;

• ###### 5. Re: SQL Puzzler: Line Up Column-Wise Rankings

Martin and Frank, and how do you know Oracle did not make two passes through the data?  Where are the explain plans showing how the query was solved?  Or is the restriction on two passes only that you cannot list the table_name in the FROM clause twice.

• ###### 6. Re: SQL Puzzler: Line Up Column-Wise Rankings

SELECT col_1_rank,

col_1_value,

REGEXP_SUBSTR (listagg (col_2_value, ',') WITHIN GROUP (ORDER BY col_2_rank) OVER (),

'[^,]+',

1,

col_1_rank)

col_2_value

FROM t

order by 1;

• ###### 7. Re: SQL Puzzler: Line Up Column-Wise Rankings

All,

The restriction is based on the number of times the table is actually traversed, not the number of times it is referenced.

I am testing out both Martin's and Frank's solutions now. I suspect Martin's is correct as it is similar to mine. Frank actually had a completely different take on it. If that one works too then, I am really glad I posted here...had not thought about pivot and unpivot.

Martin's is better than mine. If Frank's works, I suspect that the PIVOT and UNPIVOT operations will be cheaper than either WINDOWING  or a join. So, if his works, that's the even better answer.

If Another _User's answer works, it will be the best of all as it truly makes one, unsorted, pass through he data. I will give "Correct Answer" creds as soon as I can test these.

BTW, here was my solution:

WITH SORT_TARGET AS

( SELECT LISTAGG(COL_1_VAL,',') WITHIN GROUP (ORDER BY COL_1_SEQUNECE) COL_1_SEQUNECE_LIST,

LISTAGG(COL_2_VAL,',') WITHIN GROUP (ORDER BY COL_2_SEQUNECE) COL_2_SEQUNECE_LIST

FROM SORT_PUZZELER ),

LEVEL_GENERATOR AS

( SELECT LEVEL AS MUTUAL_SEQUENCE

FROM DUAL

CONNECT BY 1=1 AND

LEVEL < 11 )

SELECT LEVEL_GENERATOR.MUTUAL_SEQUENCE,

REGEXP_SUBSTR (COL_1_SEQUNECE_LIST, '[^,]+', 1, LEVEL_GENERATOR.MUTUAL_SEQUENCE ) AS COL_1_VAL,

REGEXP_SUBSTR (COL_2_SEQUNECE_LIST, '[^,]+', 1, LEVEL_GENERATOR.MUTUAL_SEQUENCE ) AS COL_2_VAL

FROM SORT_TARGET,LEVEL_GENERATOR;

Thanks, Everybody!

-Joe

• ###### 8. Re: SQL Puzzler: Line Up Column-Wise Rankings

Mark,

the plan for my query is:

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

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |      |     1 |  2027 |     6  (34)| 00:00:01 |

|   1 |  SORT ORDER BY       |      |     1 |  2027 |     6  (34)| 00:00:01 |

|   2 |   VIEW               |      |     1 |  2027 |     5  (20)| 00:00:01 |

|   3 |    WINDOW SORT       |      |     1 |    50 |     5  (20)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| T    |     1 |    50 |     4   (0)| 00:00:01 |

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

Regards

Martin

• ###### 9. Re: SQL Puzzler: Line Up Column-Wise Rankings

Hi,

Here's the paln for the UNPIVOT that I posted:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2997880065

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     8 |   200 |     7  (15)| 00:00:01 |
|   1 |  SORT GROUP BY PIVOT |         |     8 |   200 |     7  (15)| 00:00:01 |
|*  2 |   VIEW               |         |     8 |   200 |     6   (0)| 00:00:01 |
|   3 |    UNPIVOT           |         |       |       |            |          |
|   4 |     TABLE ACCESS FULL| TABLE_X |     4 |   172 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("unpivot_view"."MUTUAL_RANK" IS NOT NULL OR
"unpivot_view"."VAL" IS NOT NULL)

Note
-----
- dynamic sampling used for this statement

Why would it do more than 1 pass for an UNPIVOT?

• ###### 10. Re: SQL Puzzler: Line Up Column-Wise Rankings

Great stuff, Another _User!.

You win and Thanks!

-Joe

• ###### 11. Re: SQL Puzzler: Line Up Column-Wise Rankings

It wouldn't Frank. As you say, it would only make one pass. My response was to Mark Powell who asked for clarification about the restriction. The only one offered here that would have required two passes was Andy's above.

Yours rocks.

-J

Message was edited by: JoeUpshaw

• ###### 12. Re: SQL Puzzler: Line Up Column-Wise Rankings

Hi all,

I thougth I had a simple solution with nth_value function like this:

with tab as
(
select 1 col_1_rank, 'AAA' col_1_value, 3 col_2_rank, 'HHH' col_2_value from dual union all
select 2, 'BBB', 2, 'GGG'  from dual union all
select 3, 'CCC', 5, 'JJJ' from dual union all
select 4, 'DDD', 1, 'FFF' from dual union all
select 5, 'EEE', 4, 'III' from dual
)
select
col_1_rank
,col_1_value
,nth_value(col_2_value, col_1_rank) over (order by col_2_rank rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) a

,case col_1_rank
when 1 then nth_value(col_2_value,1) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
when 2 then nth_value(col_2_value,2) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
when 3 then nth_value(col_2_value,3) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
when 4 then nth_value(col_2_value,4) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
when 5 then nth_value(col_2_value,5) over (order by col_2_rank ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
end zz

from
tab

order by
col_1_rank
;

COL_1_RANK COL_1_VALUE A   ZZ

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

1 AAA         III FFF

2 BBB         III GGG

3 CCC         III HHH

4 DDD         III III

5 EEE         III JJJ

Can any one explain why column A shows the same value on each row?

If I use a case and a hard coded "Nth" number it works.

My database:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

Regards,

Peter

• ###### 13. Re: SQL Puzzler: Line Up Column-Wise Rankings

Here was the plan (cheapest of all)  for Another_User's:

Plan

SELECT STATEMENT  ALL_ROWSCost: 6  Bytes: 620  Cardinality: 10

3 SORT ORDER BY  Cost: 6  Bytes: 620  Cardinality: 10

2 WINDOW SORT  Cost: 6  Bytes: 620  Cardinality: 10

1 TABLE ACCESS FULL TABLE JUPSHAW.SORT_PUZZELER Cost: 4  Bytes: 620  Cardinality: 10

Franks was second best. The WINDOW SORT operation was removed but, a VIEW operation was added

Plan

SELECT STATEMENT  ALL_ROWSCost: 9  Bytes: 680  Cardinality: 20

4 SORT GROUP BY PIVOT  Cost: 9  Bytes: 680  Cardinality: 20

3 VIEW SYS. Cost: 8  Bytes: 680  Cardinality: 20

2 UNPIVOT

1 TABLE ACCESS FULL TABLE JUPSHAW.SORT_PUZZELER Cost: 4  Bytes: 620  Cardinality: 10

Mine was third, albeit the most (unnecessarily) complicated ...

Plan

SELECT STATEMENT  ALL_ROWSCost: 6  Bytes: 4,017  Cardinality: 1

7 NESTED LOOPS  Cost: 6  Bytes: 4,017  Cardinality: 1

3 VIEW JUPSHAW. Cost: 4  Bytes: 4,004  Cardinality: 1

2 SORT GROUP BY  Bytes: 62  Cardinality: 1

1 TABLE ACCESS FULL TABLE JUPSHAW.SORT_PUZZELER Cost: 4  Bytes: 620  Cardinality: 10

6 VIEW JUPSHAW. Cost: 2  Bytes: 13  Cardinality: 1

5 CONNECT BY WITHOUT FILTERING

4 FAST DUAL  Cost: 2  Cardinality: 1

Martin's was close but, had an extra view operation which increased the bytes measure.

Plan

SELECT STATEMENT  ALL_ROWSCost: 6  Bytes: 20,330  Cardinality: 10

4 SORT ORDER BY  Cost: 6  Bytes: 20,330  Cardinality: 10

3 VIEW JUPSHAW. Cost: 5  Bytes: 20,330  Cardinality: 10

2 WINDOW SORT  Cost: 5  Bytes: 620  Cardinality: 10

1 TABLE ACCESS FULL TABLE JUPSHAW.SORT_PUZZELER Cost: 4  Bytes: 620  Cardinality: 10

• ###### 14. Re: SQL Puzzler: Line Up Column-Wise Rankings

Hi,

For any practical application of this, it will be very difficult to notice any difference in performance.  How many rows will you ever deal with?  If you're considering any solution using LISTAGG, you must have a limit of about 1000 rows, and it will be very difficult to detect any difference in performance among any of these solutions with so few rows.

By the way, cost, as returned by EXPLAIN PLAN, is not an accurate measure of anything you care about.

Also, performance of single-row functions, such as REGEXP_SUBSTR, are not reflected in the EXPLAIN PLAN.

1 2 Previous Next