Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Rajeshwaran.

Asked: October 28, 2008 - 1:30 am UTC

Last updated: October 29, 2008 - 7:41 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hi Tom,

EVEN THOUGH BOTH THE QUERIES LOOK'S IDENTICAL WHY THE 2nd QUERY FUNCTION's DIFFERENT

SELECT DEPTNO,EMPNO, 
    DENSE_RANK () OVER(PARTITION BY DEPTNO ORDER BY  EMPNO ) RNK
FROM EMP

DEPTNO EMPNO RNK
10 7782 1
10 7839 2
10 7934 3
20 7369 1
20 7566 2
20 7788 3
20 7876 4
20 7902 5
30 7499 1
30 7521 2
30 7654 3
30 7698 4
30 7844 5
30 7900 6

===================================================================

SELECT DEPTNO,EMPNO, 
    DENSE_RANK () OVER(PARTITION BY 1 ORDER BY  2 ) RNK
FROM EMP

DEPTNO EMPNO RNK
20 7369 1
30 7499 1
30 7521 1
20 7566 1
30 7654 1
30 7698 1
10 7782 1
20 7788 1
10 7839 1
30 7844 1
20 7876 1
30 7900 1
20 7902 1
10 7934 1


 # HOW THE SECOND QUERY PRODUCES THE RANK ( What is the logic it uses internally to calculate the rank)

 Thanks,
 Rajesh.

and Tom said...

...

Hi Tom,

EVEN THOUGH BOTH THE QUERIES LOOK'S IDENTICAL ...


to me, they look about as different as a flying toaster oven and a banana.



Order by, in some special cases can take an ordinal position - so:

select username, user_id, created from all_users order by 2;

it the SAME as

select username, user_id, created from all_users order by user_id;

HOWEVER, that is the only clause that is like that. Specifically, partition by, group by and the like - they do not, cannot, will not use ordinal positions.

FURTHER, order by in the analytic - does not use ordinal positions.

Here are two equivalent queries:

SELECT DEPTNO,EMPNO,
DENSE_RANK () OVER(PARTITION BY 1 ORDER BY 2 ) RNK
FROM EMP
/
SELECT DEPTNO,EMPNO,
DENSE_RANK () OVER(PARTITION BY 'flying toaster ovens' ORDER BY 'bananas' ) RNK
FROM EMP
/


Those are the same. You see the number 1 and 2 in that context are literals, constants - they are not ordinal column positions.





Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 29, 2008 - 3:14 pm UTC

Tom:

I didn't understand this. Could you please explain what's partitioned?



Tom Kyte
October 29, 2008 - 4:26 pm UTC

nothing is partitioned.

If you say


Over (partition by 'flying toaster ovens' ... )

that is the same as saying

over ( /* this space left blank */ ... )

it is the same as NOT having a partition clause really.

Scott, October 29, 2008 - 4:49 pm UTC

Try

SELECT DEPTNO,EMPNO,
DENSE_RANK () OVER(PARTITION BY deptno ORDER BY empno ) RNK
FROM EMP
/

Tom Kyte
October 29, 2008 - 7:41 pm UTC

why?

that is basically their first query, with the case changed a tiny bit.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.