Results 1 to 10 of 10

Thread: Analytical functions

  1. #1
    Contributing Member
    Join Date
    Mar 2008
    Answers
    66

    Analytical functions

    Is it compulsory to sepcify order by clause in window if we use row_number() in analytical function ?

    Last edited by babi_geek; 03-15-2008 at 06:44 AM.

  2. #2
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Analytical functions

    u need to use order by because It assigns a unique number to each row to which it is applied in the ordered sequence of rows specified in the order_by_clause, beginning with 1.


  3. #3
    Contributing Member
    Join Date
    Mar 2008
    Answers
    66

    Re: Analytical functions

    Where can I get oracle documentation for analytical functions?
    I have searched SQL Documentation. I did not find explanation for analytical functions.


  4. #4
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Analytical functions


  5. #5
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Analytical functions

    Quote Originally Posted by babi_geek View Post
    Where can I get oracle documentation for analytical functions?
    I have searched SQL Documentation. I did not find explanation for analytical functions.
    Use this link


  6. #6
    Contributing Member
    Join Date
    Mar 2008
    Answers
    66

    Re: Analytical functions

    What is difference between ROLLUP and CUBE functions.

    Syntax for both these functions is same
    SELECT ... GROUP BY CUBE (grouping_column_reference_list)
    SELECT ... GROUP BY ROLLUP(grouping_column_reference_list)

    Both these functions are used for calculating totals only.

    Could anyone make it clear with one example?


  7. #7
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Analytical functions

    SELECT
    deptno,job, sum(sal) FROM scott.emp
    GROUP BY ROLLUP(deptno,job);


    SELECT
    deptno, job,sum(sal) FROM scott.emp
    GROUP BY CUBE(deptno,job);

    From the above two queries u can find the difference


  8. #8
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Analytical functions

    Please find a related article here.


  9. #9
    Contributing Member
    Join Date
    Mar 2008
    Answers
    66

    Re: Analytical functions

    Cube and rollup are extension to group by or analytical functions?
    They are not displaying group results along with rows. They are displaying only one result per one group.

    Please clarify me whether they are analytical functions or not?


  10. #10
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Analytical functions

    I think they are not analytical functions. Just extention to group by functionality.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact