What does the LISTAGG's WITHIN GROUP means when listagg is being used as reporting/analytical/window function. Because it isnt working on groups in any case i.e if main query has a GROUP BY clause or not. I know that its working on the window defined by OVER but isnt the WITHIN GROUP keyword a bit misleading/ambigous here,
I have read carefully the syntax diagram of LISTAGG and RANK functions (both diagrams of both functions ie aggregate diagram and analytic diagram). The LISTAGG analytic syntax doesnt allow orderby inside OVER()
They have seperate syntax for RANK aggregate and RANK analytic and removed the WITHIN GROUP keyword in RANK analytic and allowed the order by clause inside OVER. This seems very logical and the semantics are more easily understood/readable and the syntax/semantics is consistent/similar with other analytic functions
Why the same thing not done with LISTAGG analytic so that its more clear. ie remove WITHIN GROUP ORDER BY and allow ORDER BY inside OVER(). There is no need of WITHIN GROUP or even WITHIN,,,,,,the OVER keyword is enough to tell that aggregation will be going on the analytic window defined inside OVER and not on the GROUPs formed by the GROUP BY clause.
Actually, what i want to say is that throughout all analytic functions , the window is defined after OVER i.e (Partition Order Window), no syntax before OVER has anything to do in defining/ORDERING/framing that analytic window in any analytic function except LISTAGG 's WITHIN GROUP.
Consider this query
select a.deptno, ANY_VALUE(b.dname),
------
LISTAGG(a.ename) WITHIN GROUP (order by a.ename) as allemploysofadept,
-----
LISTAGG(ANY_VALUE(b.dname)) WITHIN GROUP (order by ANY_VALUE(b.dname)) OVER(PARTITION BY 1) AS alldeptnames
From scott.emp a, scott.dept b
Where a.deptno =b.deptno
GROUP BY a.deptno
The first LISTAGG is aggregate and the other is analytic and there is a GROUP BY clause as well, and have succesfully run it on liveoracle.
Now my question remains that the keywords WITHIN GROUP which is appearing 2 times in query, although have different semantics but will atleast confuse the reader and may seem ambigous at first look specially the WITHIN GROUP of analytic LISTAGG.
In place of, WITHIN GROUP, of analytic LISTAGG, It could have been more unambigous WITHIN PARTITION or WITHIN ANALYTIC WINDOW keywords, or something like that, but I even dont like this, I suggest the same that the whole WITHIN keyword be removed and ORDER by be allowed inside OVER().
Due to the WITHIN GROUP keyword here the reader has to force himself to assume the analytic window's Partitions as GROUPs whereas actually they are partitions of analytic window.
If GROUP BY's GROUPS are called GROUP in ORACLE then the groups of PARTITION BY Should not be given the same name or force the reader to assume the same name, although they are similar. OR if u insist that same name will be used and one should interpret its meaning from the context where the keyword is being placed in the syntax, then there was no need of a seperate keyword of PARTITION BY ie OVER (PARTITION BY....) it could be simply OVER (GROUP BY....).
I request your comments on the above ie
1. lets see if majority agrees that the WITHIN GROUP keyword is a bit ambigous in the analytic syntax of LISTAGG, and also not consistent with the syntax of other analytic functions, and your comments on
2. my suggestion of altering the syntax of analytic LISTAGG such that WITHIN GROUP order by is removed and order by is allowed inside OVER. so that:- a) its clear without any doubt of ambiguity at first look, and, b) the syntax of defining the analytic window becomes similar/consistent to the syntax of other analytic functions
3. What technical/legitimate/ logical reasons ORACLE could have, for not making the syntax of Analytic LISTAGG similar to other analytic functions, or for having a unique seperate syntax for LISTAGG.