Ran()
Streve, December 15, 2002 - 8:08 pm UTC
In your last example,
select deptno, ename, sal,
dense_rank() over ( partition by deptno
order by sal desc ) dr,
rank() over ( partition by deptno
order by sal desc ) r
from emp
order by deptno, sal desc
How do I get exactly first N records for all dept
(e.g first 4 for dept 30)?
Thanks you!
December 15, 2002 - 8:38 pm UTC
select *
from ( select a.*,
row_number() over ( partition by DEPTNO
order by SAL DESC ) rn
from emp )
where rn <= 4
order by deptno, sal desc
Check this
A reader, August 25, 2003 - 3:13 am UTC
Hi Tom,
I am using the following query to rank the cpu time usage, dont care about the sql which was took too much of cpu etc
it gives me the following , is my query wrong or is there something wrong with analsyis function.
I am using enterpise edition release 9.0.1.1.1
SQL> SELECT CPU_TIME CPUTIME,
2 RANK() OVER (ORDER BY SUM(CPU_TIME) DESC) AS CPU
3 FROM V$SQL
4 GROUP BY CPU_TIME
5 /
CPUTIME CPU
---------- ----------
20031250 1
14968750 2
13906250 3
13359375 4
6125000 5
468750 6
500000 7
531250 8
3703125 9
546875 10
703125 11
CPUTIME CPU
---------- ----------
453125 12
515625 13
2546875 14
578125 15
437500 16
406250 17
46875 18
484375 19
1906250 20
31250 21
593750 21
CPUTIME CPU
---------- ----------
78125 23
562500 24
62500 25
1578125 26
15625 27
625000 28
1250000 28
140625 30
375000 30
125000 32
968750 33
CPUTIME CPU
---------- ----------
296875 34
93750 35
421875 35
156250 37
390625 37
750000 39
734375 40
234375 41
640625 42
609375 43
109375 44
CPUTIME CPU
---------- ----------
218750 45
359375 46
171875 47
328125 48
312500 49
281250 50
265625 51
250000 52
203125 53
187500 54
0 55
55 rows selected.
SQL> SPOOL OFF
August 25, 2003 - 6:38 am UTC
it is your use of the analytic function that is improper here. Looking at your query:
SQL> SELECT CPU_TIME CPUTIME,
2 RANK() OVER (ORDER BY SUM(CPU_TIME) DESC) AS CPU
3 FROM V$SQL
4 GROUP BY CPU_TIME
5 /
in english, one might say this:
show me the CPU time -- removing all duplicates (select cpu_time, group by cpu_time)
but, before you remove the duplicates -- sum up the cpu time by cpu time -- order the data by that and rank it.
So, if you had 1,000,000 sessions that had 1 cpu second each -- you would have found the report to be:
CPUTIME CPU
---------- ----------
1 1
20031250 2
14968750 3
...
if you throw "sum(cpu_time)" into the select list, that is the number you are ranking! then it'll make more sense why "small" cpu times are at the top of the list, before larger ones. that just means you had a couple of sessions with the SAME cpu time.
Now, since I'm not sure what question you are trying to answer, I cannot tell you how to write the query. It could be as simple as "lose the sum and group by"
dense_rank()
Venkateswara Reddy, October 29, 2003 - 6:07 am UTC
Hi tom
I am enjoying with this and solving most of the problems.
I want to get TOP3 dept Salaries where SMITH works.
I am trying so many ways. I din't get. Can you tell how.
thanks in advance
Reddy
October 29, 2003 - 6:59 am UTC
select *
from ( select distinct sal
from emp
where deptno = ( select deptno from emp where ename = 'SMITH' )
order by 1 desc )
where rownum <= 3;
ANSI standards
Ashiq Shamsudeen A, October 29, 2003 - 8:11 am UTC
Hi Tom,
Are these Analytical functions are ANSI SQL standards or Oracle implementing by its own?
October 29, 2003 - 11:15 am UTC
i *believe* there are draft ansi specs for some of these, yes.
not really following that much anymore. sql92 became so large, with so many levels and options that "ansi sql 92" compliance was utterly meaningless. sql99 -- 10 times more so.
Mark, December 09, 2003 - 12:54 pm UTC
Thanks Tom for all your help. Analyatic functions do Rock!
I got a request from a buisness user wanting to change the ranking to say "(Tie)" for items that are tied. i.e.
10 SCOTT 3000 1(Tie)
10 FORD 3000 1(Tie)
10 JONES 2975 3
10 ADAMS 1100 4
20 ALLEN 1600 1
20 TURNER 1500 2
20 WARD 1250 3(Tie)
20 MARTIN 1250 3(Tie)
20 JAMES 950 5
Know of any clever ways of doing this? I'm at a loss.
Thanks
DENSE_RANK() Bug?
A reader, April 02, 2004 - 5:30 pm UTC
Tom,
I have a table T which has 1 column and 5 rows in it.
Select Col1 from T
Col1
----
5
6
7
8
5
Case 1
======
select * from
(
select rank() over(order by col1) r,
dense_rank() over(order by col1) dr,
col1
from T
)
Output
------
R DR Col1
=========
1 1 5
1 1 5
3 2 6
4 3 7
5 4 8
In the above case, the Rank() function did a skip when there was a tie whereas Dense_Rank() didn't which is expected.
Case 2
======
select * from
(
select rank() over(order by col1 desc) r,
dense_rank() over(order by col1 desc) dr,
col1
from T
)
Output
------
R DR Col1
=========
1 1 8
2 2 7
3 3 6
4 4 5
4 4 5
Why is Rank() and Dense_Rank() behaving the same in this case. The only thing I changed is to reverse the ordering of COL1 in the RANK() and DENSE_RANK() function.
I expected the R column in the last row to have a 5 instead of 4 because of a tie.
Is my understanding wrong or is it the way RANK() and DENSE_RANK() supposed to work. Please clarify.
April 02, 2004 - 7:23 pm UTC
think about it.
in the second case, you have a first, second, third and fourth place winner.
the difference would come into play when you went for FIFTH place. rank -- no 5th place, dense rank -- 5th place.
dense_rank() vs rank()
a reader, April 03, 2004 - 4:53 am UTC
As Tom explained in his answer.
insert into t values (4);
SQL> select * from t;
COL1
----------
5
5
6
7
8
4
6 rows selected.
select * from
(
select rank() over(order by col1 desc) r,
dense_rank() over(order by col1 desc) dr,
col1
from t
)
/
R DR COL1
---------- ---------- ----------
1 1 8
2 2 7
3 3 6
4 4 5
4 4 5
6 5 4
6 rows selected.
answer to the query asked by Mach from KC
dmv, April 03, 2004 - 5:21 am UTC
Hi Mark,
just check this query, I hope this will answer your question :
select ename, deptno, sal, rk,
decode(nvl(ldrk,0)-rk, 0, 'Tie', decode(rk-nvl(lgrk,0), 0 , 'Tie', rk) ) status
from
(
select ename, deptno, sal, rk,
lead(rk) over (partition by deptno order by rk) ldrk,
lag(rk) over (partition by deptno order by rk) lgrk
from (select ename, deptno, sal,
rank() over (partition by deptno order by sal desc) rk
from emp
)
);
ENAME DEPTNO SAL RK STATUS
---------- ---------- ---------- ---------- ---------------
KING 10 5000 1 1
CLARK 10 2450 2 2
MILLER 10 1300 3 3
FORD 20 3000 1 Tie
SCOTT 20 3000 1 Tie
JONES 20 2975 3 3
ADAMS 20 1100 4 4
SMITH 20 800 5 5
BLAKE 30 2850 1 1
ALLEN 30 1600 2 2
TURNER 30 1500 3 3
MARTIN 30 1250 4 Tie
WARD 30 1250 4 Tie
JAMES 30 950 6 6
A reader, April 27, 2004 - 6:42 pm UTC
using dense rank with arrays
Sam, August 05, 2004 - 5:06 pm UTC
Tom,
I have following situation. Need your opinion.
I have a table as follows
TABLE A
Amount Rate type
1000 1.80 A
1000 1.81 A
1100 1.81 A
1000 1.90 B
1100 1.95 B
Now I want result like
Amount Rate type Dense rank Amount allocated
1000 1.81 A 1 1000
1100 1.81 A 1 1100
1000 1.80 A 2 900
1100 1.95 B 1 1100
1000 1.90 B 2 900
p.s. available quantity for type A is 3000 & type B = 2000
I can use dense rank to get rank.
But further depending on available quantity I want to Update "Amount Allocated" depending on certain business logic.
My question is What is a best way to approach??
1. Read A --> use dense rank function to populate "oracle temporary" table B & update it with business logic ?
2. Read A --> use dense rank function to populate "oracle temporary" table B & then load it into associative arrays / PL/SQL table & update that with business logic & then put results in Table C.
3. Or something else
The biggest concern is the time it's going to take .
I have less than 1 sec to process.
Record count is between 100 to 3000.
Using 9iR2.
Thanks in advance
Sam
August 05, 2004 - 8:52 pm UTC
having this much level of detail - well, not quite possible to comment.
I've no clue as to what amount allocated is, does, is populated or anything.
further info
sam, August 05, 2004 - 9:04 pm UTC
Tom,
Thanks for taking time.
The amount allocated is calculated on business logic.
The logic uses few lookup tables apart from the one mentioned..
You can read first column as amount requested.
The amount allocated has to be derived based on business logic which reads rate column from current table & some lookup tables.
Thanks
Sam
August 06, 2004 - 8:01 am UTC
if the logic can be done in sql using case/decode, do it there.
if the logic can be made a plsql function, you may well be able to invoke that from sql so insert into whatever select dense-rank, yourfunction from whereever.
else select bulk collect the dense-rank, call function, forall i insert.
further question
sam, August 06, 2004 - 10:37 am UTC
What is best way to code ??
In the case where we need to look at 2 or more rows at a given time. So I guess function may not be available for me.
What I mean is in my above case I need to look at more than one row in table A to arrive at a decision to update column Amount allocated.
Need more than one row from table A to take decision.
The decision logic is business logic which make use of additional lookup tables. Should I also get those in memory ?
What is best way to approach
1. fetch rows one by one & put it in variables & do calculation ??
2. Get all required rows in array & then traverse through array to get info & make decision??
As I said earlier volume is not much.
Atmost 1000 rows to process.
Time is very crucial here. So what is best practice??
August 06, 2004 - 11:45 am UTC
lag/lead -- scalar subqueries, sql is pretty powerful.
I gave you the three approaches in order of preference above.
Convert Oracle SQL in simple SQL
A reader, February 03, 2005 - 6:22 pm UTC
Hi Tom,
I need to convert following SQL into simple SQL as the database I am using is not having dense_rank and cube function.
Here is my SQL:
select mkt, bnd, brow, part_name, max(u) as UU, sum(p) as PV from
(select decode(grouping(mkt_code),3,'All mkts',mkt_code) as mkt,
decode(grouping(bnd_name),3,'All bnds', bnd_name) as bnd,
decode(grouping(brow_type),3,'All brows',brow_type) as brow,
decode(grouping(part_name),3,'All parts', part_name) as part_name,
DENSE_RANK() OVER (PARTITION BY mkt_code, bnd_name, brow_type, part_name ORDER BY user_key) as u,
count(*) as p from
paperview pf, dateview dd, marketview md, bnd_dimension bd, brow_dimension sd, part_dimension secd
where pf.date_key=dd.date_key
and dd.fulldate='01-FEB-2000'
and pf.mkt_key=md.mkt_key
and pf.bnd_key=bd.bnd_key
and pf.brow_key=sd.brow_key
and pf.part_key=secd.part_key
and pf.spider_key=9
and pf.user_key > 19
group by cube (mkt_code,bnd_name,brow_type,part_name), user_key)
group by mkt, bnd, brow, part_name
Pls help me converting it if possible ?
Thanks
February 04, 2005 - 1:39 am UTC
thats too bad. I mean, not everything is possible in "simple sql"
this is one of them.
well, two of them.
you'll have to write a report generator (pretty boring stuff...). you won't do this in "simple sql" whatever that is.
RE: Not exactly a dense_rank
Bakunian, September 12, 2005 - 1:15 pm UTC
DEPTNO ENAME SAL DR
---------- ---------- ---------- ----------
10 KING 5000 1
CLARK 2450 2
MILLER 1300 3
20 SCOTT 3000 1 <-
FORD 3000 1 <-
JONES 2975 2
ADAMS 1100 3
30 BLAKE 2850 1
ALLEN 1600 2
TURNER 1500 3
Based on your above example of EMP table, how can I make sure that Scott and Ford in DEPTNO 20 get sequential rank even though they are tie. Therefore, Scott would be first if he is first occurrence in table and Ford is second and so on.
We are trying to address a bug by updating 13 million rows assigning sequential rank to items within each group based on its position within each group and there are no other discriminating criteria.
We use Oracle 9.2
Thank you for your time.
September 12, 2005 - 2:26 pm UTC
are you looking for row_number()?? it does 1, 2, 3, 4, ....
Another Solution for Mark's Question (December 09, 2003)
Su Baba, September 14, 2005 - 1:27 pm UTC
SELECT deptno, ename, sal, next_sal, prev_sal,
DECODE(sal, next_sal, '(Tie)', prev_sal, '(Tie)') tie
FROM (
SELECT deptno, ename, sal,
LAG(sal) OVER (PARTITION BY deptno
ORDER BY sal DESC) next_sal,
LEAD(sal) OVER (PARTITION BY deptno
ORDER BY sal DESC) prev_sal
FROM scott.emp
);
DEPTNO ENAME SAL NEXT_SAL PREV_SAL TIE
--------- ---------- --------- --------- --------- -----
10 KING 5000 2450
CLARK 2450 5000 1300
MILLER 1300 2450
20 SCOTT 3000 3000 (Tie)
FORD 3000 3000 2975 (Tie)
JONES 2975 3000 1100
ADAMS 1100 2975 800
SMITH 800 1100
30 BLAKE 2850 1600
ALLEN 1600 2850 1500
TURNER 1500 1600 1250
WARD 1250 1500 1250 (Tie)
MARTIN 1250 1250 950 (Tie)
JAMES 950 1250
sql query
manas, September 25, 2005 - 5:24 pm UTC
hallo tom
how to get top 2 salary from a emp table per each deptno?
September 25, 2005 - 8:03 pm UTC
sort of an ambigous query - but I'll answer in the spirit of the question:
scott@ORA10G> l
1 select distinct deptno, sal
2 from (
3 select deptno, sal, dense_rank() over (partition by deptno order by sal DESC nulls last) dr
4 from emp
5 )
6* where dr <= 2
scott@ORA10G> /
DEPTNO SAL
---------- ----------
10 5000
10 2450
20 3000
20 2975
30 2850
30 1600
6 rows selected.
Those are the top two salaries by deptno...
Ami, April 13, 2006 - 10:28 pm UTC
Hi Tom,
I need to create a report from a table(10million recs) that has a "house_points" column. Beginning with the highest house_points value (order by house_points desc), I need to create groups of 100 records each and sum(house_points) and get the max(house_points), min(house_points) for each group. Any ideas?
Any advice is greatly appreciated.
Thanks,
Ami
April 14, 2006 - 12:22 pm UTC
select min(..) , max(...)
from (
select a.*, trunc((rownum-1)/100) grp
from (select * from t order by house_points desc)
)
group by grp;
dense_rank bug?
A reader, June 09, 2011 - 5:40 pm UTC
I found a strange behavior of dense_rank function. I believe it's a bug and is only reproducible on Oracle 11g.
SQL>
SQL> SELECT * FROM v$version;
BANNER
--------------------------------------------------------------------------------
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
SQL>
SQL> DROP TABLE b;
Table dropped.
SQL> DROP TABLE a;
Table dropped.
SQL>
SQL> CREATE TABLE a (
2 a_id INTEGER,
3 Name VARCHAR2(5)
4 );
Table created.
SQL>
SQL> CREATE TABLE b (
2 b_id INTEGER,
3 a_id INTEGER,
4 Status INTEGER
5 );
Table created.
SQL>
SQL> INSERT INTO a
2 SELECT level, 'A' || level
3 FROM dual
4 CONNECT BY level <= 10;
10 rows created.
SQL>
SQL> -- ---------------------------------------------------------------------------
SQL> -- Procedure for populating table b.
SQL> --
SQL> -- The input parameter determines the ratio of the number of records in b to
SQL> -- that of table a.
SQL> -- ---------------------------------------------------------------------------
SQL> CREATE OR REPLACE PROCEDURE Populate_b (
2 p_num_of_b_per_a INTEGER
3 )
4 AS
5 l_cnt INTEGER := 0;
6
7 CURSOR c IS
8 SELECT a_id
9 FROM a;
10
11 BEGIN
12 DELETE FROM b;
13
14 FOR x IN c LOOP
15 FOR i IN 1..p_num_of_b_per_a LOOP
16 l_cnt := l_cnt + 1;
17
18 INSERT INTO b VALUES (l_cnt, x.a_id, i);
19 END LOOP;
20 END LOOP;
21
22 END;
23 /
Procedure created.
SQL>
SQL> -- ---------------------------------------------------------------------------
SQL> -- correct result
SQL> -- ---------------------------------------------------------------------------
SQL> exec Populate_b(1);
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT name, dr
2 FROM (
3 SELECT Name,
4 row_number() OVER (PARTITION BY a.Name ORDER BY b.Status) rn,
5 dense_rank() OVER (ORDER BY a.Name) dr
6 FROM a, b
7 WHERE a.a_id = b.a_id
8 )
9 WHERE dr <= 20 AND
10 rn = 1;
NAME DR
----- ----------
A1 1
A10 2
A2 3
A3 4
A4 5
A5 6
A6 7
A7 8
A8 9
A9 10
10 rows selected.
SQL>
SQL> -- ---------------------------------------------------------------------------
SQL> -- Expect to get 10 records, but get 7 instead. Gets worse as we increase
SQL> -- the ratio of records between table a and b.
SQL> -- ---------------------------------------------------------------------------
SQL> exec Populate_b(3);
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT name, dr
2 FROM (
3 SELECT Name,
4 row_number() OVER (PARTITION BY a.Name ORDER BY b.Status) rn,
5 dense_rank() OVER (ORDER BY a.Name) dr
6 FROM a, b
7 WHERE a.a_id = b.a_id
8 )
9 WHERE dr <= 20 AND
10 rn = 1;
NAME DR
----- ----------
A1 1
A10 2
A2 3
A3 4
A4 5
A5 6
A6 7
7 rows selected.
SQL>
SQL> exec Populate_b(5);
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT name, dr
2 FROM (
3 SELECT Name,
4 row_number() OVER (PARTITION BY a.Name ORDER BY b.Status) rn,
5 dense_rank() OVER (ORDER BY a.Name) dr
6 FROM a, b
7 WHERE a.a_id = b.a_id
8 )
9 WHERE dr <= 20 AND
10 rn = 1;
NAME DR
----- ----------
A1 1
A10 2
A2 3
A3 4
A4 5
SQL>
SQL> exec Populate_b(10);
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT name, dr
2 FROM (
3 SELECT Name,
4 row_number() OVER (PARTITION BY a.Name ORDER BY b.Status) rn,
5 dense_rank() OVER (ORDER BY a.Name) dr
6 FROM a, b
7 WHERE a.a_id = b.a_id
8 )
9 WHERE dr <= 20 AND
10 rn = 1;
NAME DR
----- ----------
A1 1
A10 2
A2 3
SQL>
SQL> exec Populate_b(20);
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT name, dr
2 FROM (
3 SELECT Name,
4 row_number() OVER (PARTITION BY a.Name ORDER BY b.Status) rn,
5 dense_rank() OVER (ORDER BY a.Name) dr
6 FROM a, b
7 WHERE a.a_id = b.a_id
8 )
9 WHERE dr <= 20 AND
10 rn = 1;
NAME DR
----- ----------
A1 1
A10 2
SQL>
SQL>
SQL> -- ---------------------------------------------------------------------------
SQL> -- Convert the dense_rank to number and the result is correct. This is very
SQL> -- bizarre!
SQL> -- ---------------------------------------------------------------------------
SQL> SELECT name, dr
2 FROM (
3 SELECT Name,
4 row_number() OVER (PARTITION BY a.Name ORDER BY b.Status) rn,
5 dense_rank() OVER (ORDER BY a.Name) dr
6 FROM a, b
7 WHERE a.a_id = b.a_id
8 )
9 WHERE TO_NUMBER(dr) <= 20 AND
10 rn = 1;
NAME DR
----- ----------
A1 1
A10 2
A2 3
A3 4
A4 5
A5 6
A6 7
A7 8
A8 9
A9 10
10 rows selected.
SQL>
SQL>
SQL> spool off
June 10, 2011 - 7:51 am UTC
definitely a wrong answer.
do you have access to support? If not, let me know and I'll file the bug.
Oleksandr Alesinskyy, June 10, 2011 - 8:46 am UTC
Cannot reproduce in 11.1.0.7 (32-bit Windows).
June 10, 2011 - 2:44 pm UTC
I reproduced in 11.2.0.2 on Linux
Can replicate in AIX also
Albert Nelson A, June 15, 2011 - 5:12 am UTC
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> create table test (c number, n number);
Table created.
SQL> insert into test values (1,1);
1 row created.
SQL> insert into test values (1,2);
1 row created.
SQL> insert into test values (2,1);
1 row created.
SQL> insert into test values (2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> -- Correct
SQL> select *
2 from (select t.c,
3 t.n,
4 dense_rank() over(order by t.n) dr
5 from test t)
6 where dr <= 2;
C N DR
---------- ---------- ----------
1 1 1
2 1 1
1 2 2
2 2 2
SQL> -- Wrong
SQL> select *
2 from (select t.c,
3 t.n,
4 dense_rank() over(partition by t.n order by t.c) rn,
5 dense_rank() over(order by t.n) dr
6 from test t)
7 where dr <= 2;
C N RN DR
---------- ---------- ---------- ----------
1 1 1 1
2 1 2 1
1 2 1 2
SQL> select *
2 from (select t.c,
3 t.n,
4 row_number() over(partition by t.n order by t.c) rn,
5 dense_rank() over(order by t.n) dr
6 from test t)
7 where dr <= 2;
C N RN DR
---------- ---------- ---------- ----------
1 1 1 1
2 1 2 1
1 2 1 2
SQL> select *
2 from (select t.c,
3 t.n,
4 lag(t.n) over(partition by t.n order by t.c) rn,
5 dense_rank() over(order by t.n) dr
6 from test t)
7 where dr <= 2;
C N RN DR
---------- ---------- ---------- ----------
1 1 1
2 1 1 1
1 2 2
SQL> select *
2 from (select t.c,
3 t.n,
4 rank() over(partition by t.n order by t.c) rn,
5 dense_rank() over(order by t.n) dr
6 from test t)
7 where dr <= 2;
C N RN DR
---------- ---------- ---------- ----------
1 1 1 1
2 1 2 1
1 2 1 2
June 17, 2011 - 12:19 pm UTC
I went ahead and filed bug 12670181 for this
Need your help
AKP, December 21, 2013 - 8:32 am UTC
Hello Tom,
create table t1_objs as select object_type, object_name from all_objects
create index idx_t1_objs on t1_objs (object_type )
Query 1 :
select decode(row_number() over (partition by object_type order by object_name),1,object_type,'') object_type,object_name
from t1_objs
where object_type in ('TABLE','TRIGGER','INDEX')
order by object_name, object_type
Query 2 :
select object_type, object_name,
dense_rank() over ( partition by object_type
order by object_name desc ) dr,
rank() over ( partition by object_type
order by object_name desc ) r
from t1_objs
where object_type in ('TABLE','TRIGGER','INDEX')
order by object_type, object_name
Using Query 1, I can avoid displaying of object_type if it's gets reapeatd. How can I acheieve the same through Query 2?
I have seen you have already given the example but that can only be run in SQLPlus. I need to execute in the PL/SQL which I'll be sending as ref cursor.
/code><code>
/code><code>, February 27, 2015 - 1:05 pm UTC
Asim, July 29, 2022 - 12:01 am UTC
I am trying to understand analytics, to confirm my understandings are correct I want to know that:-
If OVER (.... ORDER BY.) have no ties and have only unique rows then there is no differrence between the results of ROWNUMBER, RANK and DENS_RANK functions, correct?
Thanks
July 29, 2022 - 3:27 pm UTC
Correct - if sorting on unique values these all give the same results
Suggestion
Asim, July 29, 2022 - 3:44 pm UTC
Thanks for your reply.
Now if the above is correct , wouldnt it be better to have one function with 1 parameter which can have 3 values only, instead of 3 seperate functions. ie
ROW_NUMBER(ROW_NUMBER|RANK|DENSE_RANK) ,and ROW_NUBER being the default)
Row_number = row_number() = row_number(row_number)
Rank = row_number(RANK)
DENSE_RANK = row_number(DENSE_RSNK)
August 01, 2022 - 3:08 pm UTC
How is this better than the current approach with different functions?
Doesnt make much difference
Asim, August 02, 2022 - 10:32 am UTC
Hmmmm, it doesnt make much diffference and I think none of them is better than the other, but for some people, they like seperate functions, and some other people like one function with 1 parameter of 3 values.
August 02, 2022 - 4:00 pm UTC
Again, this is standard SQL functionality. You'll have to come up with a very compelling reason to add a parameter that affects the behaviour of these for it to happen.