Home » SQL & PL/SQL » SQL & PL/SQL » Analytic Functions
Analytic Functions [message #184250] Tue, 25 July 2006 21:37 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
just wanna ask on analytic functions regarding the ORDER BY part =)


SQL> ed
Wrote file afiedt.buf

  1  select *
  2  from (select deptno, ename, sal
  3        ,dense_rank() over (partition by deptno order by sal desc) rank
  4        from emp)
  5  where rank <= 3
  6* order by deptno, sal desc
SQL> /

    DEPTNO ENAME             SAL       RANK
---------- ---------- ---------- ----------
        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
           WARD             1250          3
           MARTIN           1250          3


11 rows selected.

SQL>



why is that i just added ename on the ORDER BY part of the DENSE_RANK and then



SQL> ed
Wrote file afiedt.buf

  1  select *
  2  from (select deptno, ename, sal
  3        ,dense_rank() over (partition by deptno order by sal desc, ename) ran
k
  4        from emp)
  5  where rank <= 3
  6* order by deptno, sal desc
SQL> /

    DEPTNO ENAME             SAL       RANK
---------- ---------- ---------- ----------
        10 KING             5000          1
           CLARK            2450          2
           MILLER           1300          3

        20 FORD             3000          1
           SCOTT            3000          2
           JONES            2975          3

        30 BLAKE            2850          1
           ALLEN            1600          2
           MARTIN           1250          3


9 rows selected.

SQL>



ADAMS and WARD we're removed from the result, why is it? did it rank it as UNIQUE per sal and ename? also if you could give any much more simple tutorial on analytics, and it's range on your query, the usual tutorials i find are that difficult for me to understand =) thanks again sir/mam =)

[Updated on: Tue, 25 July 2006 22:01]

Report message to a moderator

Re: Analytic Functions [message #184261 is a reply to message #184250] Tue, 25 July 2006 22:53 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
They're rank is 4.
Re: Analytic Functions [message #184264 is a reply to message #184261] Tue, 25 July 2006 22:59 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
yep, am i right that order by on the dense_rank affects the distinction of rows? because on the previous row with only sal it didnt considered the ename for ranking, and then added ename which made the rows distinct on the ename so thats why adams and ward was moved to rank 4, therefore eliminating them from the resultset =)
Re: Analytic Functions [message #184278 is a reply to message #184264] Wed, 26 July 2006 00:36 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
yes. when you do order by in the group with only sal SCOTT and FORD got the same rank as they are having the same salary. whereas when you do the same with ename rows getting distinct. so ADAMS gets the rank as 4. so it eleminated from the result set due to the where clause.

i hope it is clear
Re: Analytic Functions [message #184286 is a reply to message #184278] Wed, 26 July 2006 00:56 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
yep got it! im having a problem though with the last_value function =)


SQL> ed
Wrote file afiedt.buf

  1  select deptno, ename, sal
  2        ,first_value(ename) over (partition by deptno order by sal asc) "lowe
st salary"
  3        ,last_value(ename) over (partition by deptno order by sal asc) "highe
st salary"
  4* from emp
SQL> /

 DEPTNO ENAME          SAL lowest sal highest sa
------- ---------- ------- ---------- ----------
     10 MILLER        1300 MILLER     MILLER
        CLARK         2450 MILLER     CLARK
        KING          5000 MILLER     KING

     20 SMITH          800 SMITH      SMITH
        ADAMS         1100 SMITH      ADAMS
        JONES         2975 SMITH      JONES
        FORD          3000 SMITH      SCOTT
        SCOTT         3000 SMITH      SCOTT

     30 JAMES          950 JAMES      JAMES
        MARTIN        1250 JAMES      WARD
        WARD          1250 JAMES      WARD
        ALLEN         1600 JAMES      ALLEN
        BLAKE         2850 JAMES      BLAKE


13 rows selected.

SQL>



why does the highest salary column displays it wrong? i think it should display the last row, which is KING, SCOTT, and BLAKE? why is it displaying also the other ename? thanks again =)
Re: Analytic Functions [message #184287 is a reply to message #184286] Wed, 26 July 2006 01:01 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ehegagoka wrote on Wed, 26 July 2006 07:56

why does the highest salary column displays it wrong? i think it should display the last row, which is KING, SCOTT, and BLAKE? why is it displaying also the other ename? thanks again =)

It shows the results as it goes. Assume that SQL fetches the first row for department 10. Then the last value for department 10 fetched so far is: MILLER, then it fetches the next row and sees that the salary is higher so it becomes CLARK... With FIRST_VALUE, you don't have this issue because, well, there's only the first to fetch...

Here's an idea: have you tried using FIRST_VALUE with a reversed order by?

MHE

[Updated on: Wed, 26 July 2006 01:01]

Report message to a moderator

Re: Analytic Functions [message #184290 is a reply to message #184287] Wed, 26 July 2006 01:10 Go to previous messageGo to next message
Littlefoot
Messages: 20824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Also, you might try to omit ORDER BY in analytic functions and add it at the end of the query, such as
SQL> SELECT deptno, ename, sal,
  2    first_value(ename) over (PARTITION BY deptno) fv,
  3    last_value(ename) over (PARTITION BY deptno) lv
  4  FROM EMP
  5  ORDER BY deptno, sal
  6  ;

    DEPTNO ENAME             SAL FV         LV
---------- ---------- ---------- ---------- ----------
        10 MILLER           1300 MILLER     KING
           CLARK            2450 MILLER     KING
           KING             5000 MILLER     KING

        20 SMITH             800 SMITH      FORD
           ADAMS            1100 SMITH      FORD
           JONES            2975 SMITH      FORD
           SCOTT            3000 SMITH      FORD
           FORD             3000 SMITH      FORD

        30 JAMES             950 JAMES      BLAKE
           BURGLAR          1250 JAMES      BLAKE
           TURNER           1500 JAMES      BLAKE
           ALLEN            1600 JAMES      BLAKE
           BLAKE            2850 JAMES      BLAKE
Re: Analytic Functions [message #184292 is a reply to message #184287] Wed, 26 July 2006 01:11 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
so that was it! yep the query works fine by using DESC on the order by of first_value, i thought it works on a way where it fetches all the rows for a group, lets say group for deptno of 10, then it gets the first and last record for that group at once. thank you so much again sir =)
Re: Analytic Functions [message #445154 is a reply to message #184250] Fri, 26 February 2010 04:26 Go to previous messageGo to next message
preetm1
Messages: 14
Registered: February 2010
Location: hyd
Junior Member
Hi,

Im using the row_number() to generate a sequence for a query output.
The sequence is geeting printed correctly if the query output is less than 10 rows.But if the query output is more than 10 rows the problem is arising and it is as below

1
10
11
12
..
..
2
20
21
22
..
..
30
31
32
..
..

I would want the sequnec to get printed as
1
2
3
..
..
10
11
12
..
..
20
21
22
..
..
30
31
32
..
..

looking forward for help on this issue.

Thanks in advance
Re: Analytic Functions [message #445159 is a reply to message #445154] Fri, 26 February 2010 04:37 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Do not hijack others topic for a different question, create your own
2/ Do not multi-post the same question

=> Above all DO NOT hijack several topics to post the same question.

Regards
Michel
Previous Topic: Week problem.
Next Topic: Commit and Ref cursor from GLOBAL TEMPORARY TABLE
Goto Forum:
  


Current Time: Sat Sep 24 23:10:15 CDT 2016

Total time taken to generate the page: 0.12560 seconds