OraFAQ Forum: SQL & PL/SQL » Distinct & Group By when used Analytic Function

Home » SQL & PL/SQL » SQL & PL/SQL » Distinct & Group By when used Analytic Function

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Distinct & Group By when used Analytic Function [message #612170]

Mon, 14 April 2014 00:21

mist598
Messages: 1195
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

Report message to a moderator

Re: Distinct & Group By when used Analytic Function [message #612173 is a reply to message #612170]

Mon, 14 April 2014 02:13

Michel Cadot
Messages: 68647
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?

Report message to a moderator

Re: Distinct & Group By when used Analytic Function [message #612174 is a reply to message #612173]

Mon, 14 April 2014 02:20

mist598
Messages: 1195
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)

Report message to a moderator

Previous Topic:	Historical Data Delete Query
Next Topic:	can any one help me in forming the below requested query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 16:54:06 CDT 2024