Home » SQL & PL/SQL » SQL & PL/SQL » Distinct & Group By when used Analytic Function
Distinct & Group By when used Analytic Function [message #612170] Mon, 14 April 2014 00:21 Go to next message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Hi ,

Can you please explain? Why i am getting different results and what is the difference's of Distinct & Group By.



SQL> SELECT deptno, COUNT (*) OVER () AS empcount
  2    FROM emp
  3  GROUP BY deptno
  4  /

    DEPTNO   EMPCOUNT
---------- ----------
        10          3
        20          3
        30          3

SQL> SELECT DISTINCT deptno, COUNT (*) OVER () AS empcount
  2             FROM emp
  3         ORDER BY deptno
  4  /

    DEPTNO   EMPCOUNT
---------- ----------
        10         14
        20         14
        30         14
Re: Distinct & Group By when used Analytic Function [message #612173 is a reply to message #612170] Mon, 14 April 2014 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Analytic functions are computed after GROUP BY and before DISTINCT.
The first one does not give EMPCOUNT but DEPTCOUNT.

What are you trying to get with these queries?

Re: Distinct & Group By when used Analytic Function [message #612174 is a reply to message #612173] Mon, 14 April 2014 02:20 Go to previous message
mist598
Messages: 991
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:

Analytic functions are computed after GROUP BY and before DISTINCT.
The first one does not give EMPCOUNT but DEPTCOUNT.

What are you trying to get with these queries?


Thank You Michel... Smile

i want know difference's of Distinct & Group By(Using in the Analytic function)
Previous Topic: Historical Data Delete Query
Next Topic: can any one help me in forming the below requested query
Goto Forum:
  


Current Time: Fri Oct 31 15:58:44 CDT 2014

Total time taken to generate the page: 0.15349 seconds