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: 1059
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: 59997
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: 1059
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: Sat Dec 20 08:51:06 CST 2014

Total time taken to generate the page: 1.63447 seconds