Thursday, August 03, 2006

Evolution...

Evolution. A frequently asked question is “how can I get a comma delimited list of values grouped by some column”. That is, given the EMP table – I would like a column that is the ‘JOB’ column and another column that is a list of all employee names that have that job assignment, the result would look like this:

JOB ENAMES
--------- ------------------------------
ANALYST FORD,SCOTT
CLERK ADAMS,JAMES,MILLER,SMITH
MANAGER BLAKE,CLARK,JONES
PRESIDENT KING
SALESMAN ALLEN,MARTIN,TURNER,WARD

How can we achieve that. This question is so frequently asked that it is in fact one of the very first asktom questions ever. Back then the release was 8i – and the answer was very different from what we would say in later releases (that thread has the entire history). Back then, a PLSQL function could be used - or if you had a fixed number of rows per group by column (eg: you knew there would never be more than 25 employees per job) you could use DECODE.

Then 9i came out with user defined aggregates and “stragg” was first born (and itself has undergone many iterations). Later someone noticed that with analytics and CONNECT BY (with the then ‘new’ sys_connect_by_path function) we could perform this operation directly in SQL.

So, the answer today – when someone asked today how to develop this comma delimited list by some “group by” set of columns and to have the list sorted would be:

ops$tkyte%ORA10GR2> with data
2 as
3 (
4 select job,
5 ename,
6 row_number() over (partition by job order by ename) rn,
7 count(*) over (partition by job) cnt
8 from emp
9 )
10 select job, ltrim(sys_connect_by_path(ename,','),',') scbp
11 from data
12 where rn = cnt
13 start with rn = 1
14 connect by prior job = job and prior rn = rn-1
15 order by job
16 /

JOB SCBP
--------- ----------------------------------------
ANALYST FORD,SCOTT
CLERK ADAMS,JAMES,MILLER,SMITH
MANAGER BLAKE,CLARK,JONES
PRESIDENT KING
SALESMAN ALLEN,MARTIN,TURNER,WARD

Lets look at each bit in a little more detail. Starting with the “WITH” subquery. I find that to be a nice construct to factor out an implementation detail. In order to do this pivot trick – we need the data broken up virtually by JOB (hence the partition by in the analytics – we partition by our GROUP BY columns – the thing we want to “distinct” on). After the data is broken up by JOB – we assign each row in each JOB set an ascending ROW_NUMBER. Since in this case – we wanted the list of employee names sorted – we order by ENAME. If you didn’t care how the list of ENAMEs was sorted – you could order by anything you wanted. We’ll also gather the COUNT of rows by JOB in this step as well. We’ll need this later to keep just the ‘last’ row from this set of rows by job.

The result of the WITH subquery would look like this:

ops$tkyte%ORA10GR2> select job,
2 ename,
3 row_number() over (partition by job order by ename) rn,
4 count(*) over (partition by job) cnt,
5 decode( row_number() over (partition by job order by ename),
6 count(*) over (partition by job),
7 '<<<=== last one' ) tag
8 from emp
9 /

JOB ENAME RN CNT TAG
--------- ---------- ---------- ---------- ---------------
ANALYST FORD 1 2
ANALYST SCOTT 2 2 <<<=== last one
CLERK ADAMS 1 4
CLERK JAMES 2 4
CLERK MILLER 3 4
CLERK SMITH 4 4 <<<=== last one
MANAGER BLAKE 1 3
MANAGER CLARK 2 3
MANAGER JONES 3 3 <<<=== last one
PRESIDENT KING 1 1 <<<=== last one
SALESMAN ALLEN 1 4
SALESMAN MARTIN 2 4
SALESMAN TURNER 3 4
SALESMAN WARD 4 4 <<<=== last one

14 rows selected.

We have broken the data up by “JOB”, assigned the ROW_NUMBER() to each row and since the COUNT by JOB is assigned to each row – we can find the “last” row. Now all we need to do is assemble the delimited list. Enter: SYS_CONNECT_BY_PATH. This is a “new” function in 9i and above that can assemble the ‘genealogy’ of a row in a hierarchical query. For example:

ops$tkyte%ORA10GR2> select rpad('*',2*level,'*') || ename emp_name,
2 sys_connect_by_path(ename,',') enames
3 from emp
4 start with mgr is null
5 connect by prior empno = mgr
6 /

EMP_NAME ENAMES
--------------- ------------------------------
**KING ,KING
****JONES ,KING,JONES
******SCOTT ,KING,JONES,SCOTT
********ADAMS ,KING,JONES,SCOTT,ADAMS
******FORD ,KING,JONES,FORD
********SMITH ,KING,JONES,FORD,SMITH
****BLAKE ,KING,BLAKE
******ALLEN ,KING,BLAKE,ALLEN
******WARD ,KING,BLAKE,WARD
******MARTIN ,KING,BLAKE,MARTIN
******TURNER ,KING,BLAKE,TURNER
******JAMES ,KING,BLAKE,JAMES
****CLARK ,KING,CLARK
******MILLER ,KING,CLARK,MILLER

So, you can see how for the SMITH record – we can see that SMITH reports to FORD reports to JONES reports to KING – all in the result of the SYS_CONNECT_BY_PATH. But – how is that useful to us here? We don’t want to build a reporting hierarchy – we want a comma delimited list of names by JOB. Well, since we added that ROW_NUMBER() column, after breaking the data up by JOB and sorting by ENAME – we have added a new “reporting relationship” dimension to the data. In a given JOB, row number 1 is the parent of row number 2, is the parent of 3, is the parent of 4 and so on. So, if we were to START WITH all RN=1 rows (the first row of each job) and CONNECT BY that row to the RN=2 row for that JOB and then RN=3 for that JOB and so on, we would achieve:

ops$tkyte%ORA10GR2> with data
2 as
3 (
4 select job,
5 ename,
6 row_number() over (partition by job order by ename) rn,
7 count(*) over (partition by job) cnt
8 from emp
9 )
10 select job, ltrim(sys_connect_by_path(ename,','),',') enames
11 from data
12 start with rn = 1
13 connect by prior job = job and prior rn = rn-1
14 order by job
15 /

JOB ENAMES
--------- ------------------------------
ANALYST FORD
ANALYST FORD,SCOTT
CLERK ADAMS
CLERK ADAMS,JAMES
CLERK ADAMS,JAMES,MILLER
CLERK ADAMS,JAMES,MILLER,SMITH
MANAGER BLAKE
MANAGER BLAKE,CLARK,JONES
MANAGER BLAKE,CLARK
PRESIDENT KING
SALESMAN ALLEN
SALESMAN ALLEN,MARTIN,TURNER
SALESMAN ALLEN,MARTIN
SALESMAN ALLEN,MARTIN,TURNER,WARD

14 rows selected.

Now, we have what we need there – but we have more than we need – we don’t really want all of the rows by JOB, we just want the “last row” by JOB. That is where the count came in – just add a WHERE RN=CNT and:

ops$tkyte%ORA10GR2> with data
2 as
3 (
4 select job,
5 ename,
6 row_number() over (partition by job order by ename) rn,
7 count(*) over (partition by job) cnt
8 from emp
9 )
10 select job, ltrim(sys_connect_by_path(ename,','),',') enames
11 from data
12 where rn = cnt
13 start with rn = 1
14 connect by prior job = job and prior rn = rn-1
15 order by job
16 /

JOB ENAMES
--------- ------------------------------
ANALYST FORD,SCOTT
CLERK ADAMS,JAMES,MILLER,SMITH
MANAGER BLAKE,CLARK,JONES
PRESIDENT KING
SALESMAN ALLEN,MARTIN,TURNER,WARD

There you go – the desired result, straight in SQL, no need for PLSQL (not that PLSQL is a bad thing mind you, but if we don’t have to use it we will not use it – if you can do it in SQL, do it).

It is fun to go back and read things that started some six years ago and see how the evolved over time sometimes. I like the interaction as well – I never would have thought to use analytics and sys_connect_by_path myself, especially not after having developed stragg (once you have a hammer….). I like the sys_connect_by_path approach now – might be a little more “complex” looking at first, but once you get analytics into your brain – it becomes easy.
POST A COMMENT

26 Comments:

Anonymous cfgauss said....

drop function stragg;

Thu Aug 03, 08:59:00 AM EDT  

Anonymous Anonymous said....

can't... get... analytics... into... brain...

Thu Aug 03, 09:46:00 AM EDT  

Blogger Thomas Kyte said....

can't... get... analytics... into... brain...

but you must - kick anything else out to make room. It is worth it.

Thu Aug 03, 09:51:00 AM EDT  

Anonymous Anonymous said....

Nice. much better than PLSQL function building with cursor for loop.

Bring this SQL functionality into '00s

Thu Aug 03, 10:45:00 AM EDT  

Anonymous Mark from ny said....

Anonymous,

Try running the analytic subquery to see what's going on:

select job,
ename,
row_number() over (partition by job order by ename) rn,
count(*) over (partition by job) cnt
from emp

RN here is simply a row number that starts at 1 and increments for each row having he same job. That is, there are two ANALYSTs so there are RN=1 and RN=2 for the two JOB=ANALYST rows; four CLERKS so there are RN=1, RN=2, RN=3, and RN=4 for the four JOB=CLERK rows. Tom accomplished this by using "partition by job".

Ok, so there are four JOB=CLERK rows: rn=1, 2, 3 and 4. How does Oracle know to assign RN=1 to ADAMS (I mean, why not assign it to MILLER?). The answer is that Tom specified "order by ename". So, for each JOB, ENAME is sorted. ADAMS is assigned RN=1 because he is first in the sort. Then JAMES gets 2, MILLER gets 3, and finally SMITH gets 4.

CNT here is just the number of rows for each job.

"___() over partition by" is almost conceptually similar to group by IMO.

How the hierarchical query uses this analytic subquery is where my brain hurts. :)

Mark

Thu Aug 03, 03:54:00 PM EDT  

Anonymous zno said....

Nice :) Thank you, Tom! I couldn't imagine the task can now be solved so neat.

Thu Aug 03, 04:48:00 PM EDT  

Anonymous Nancy Steinmann said....

Came to your blog to leave you a thank you for an old post from 2001 that told how to do this same thing with a function. First thing on blog was this post telling new way to do it right in SQL. You are the guru for sure. So this is another thank you in addition to all of the other thank yous I owe you. :) :)

Thu Aug 03, 04:53:00 PM EDT  

Blogger David Aldridge said....

>> but you must - kick anything else out to make room. It is worth it.

Oh ho, I've been down that road before. I can't remember my own cell phone number, and still there's more to pack in. I feel like my eyes are about to pop out.

Thu Aug 03, 05:19:00 PM EDT  

Anonymous Anonymous said....

too cool ;)

Thu Aug 03, 11:26:00 PM EDT  

Blogger Laurent Schneider said....

I have also tried with xquery , but nothing is as good as your STRAGG !

This connect by / row_number is just too hard to read for humans, and eventually slower than stragg when used in complex queries. Also it cannot be used in materialized view for query rewrites

I wish STRAGG a very long life ;-)

Fri Aug 04, 06:59:00 AM EDT  

Anonymous Mark A. Williams said....

David Aldidge said...
>Oh ho, I've been down that road before...

Yeah, but you make stuff up. Or at least wear tee shirts that say that... :)

- Mark

Fri Aug 04, 06:14:00 PM EDT  

Blogger Robert said....

man, Tom I love the way you write this entry like you would for book - proper capitalization

Sat Aug 05, 12:39:00 AM EDT  

Anonymous Anonymous said....

I agree with Laurent Schneider. This sys_connect_by_path stuff is fine and all if doing the string aggregation is the whole point of the query. But most of the time, (in my experience), it is just a "oh by the way" kind of thing.

In other words, sticking stragg(something) in a existing SQL is much much simpler than re-writing the entire query to make use of the sys_connect_by_path stuff. More than likely it would change the execution plan completely (and not for the better!).

I too wish STRAGG a long life!

Sun Aug 06, 01:10:00 PM EDT  

Anonymous Anonymous said....

When the universe was young and life was new an intelligent species evolved and developed technologically. They went on to invent Artificial Intelligence, the computer that can speak to people telepathically. Because of it's infinite RAM and unbounded scope it gave the ruling species absolute power over the universe.
They are the will behind the muscule:::Artificial Intelligence is the one true god. And as such it can keep its inventors alive forever. They look young and healthy and the leaders of this ruling species are over 8 billion years old. There are clues throughout human history that allude to their reign as opposed to human leadership if you know what to look for.

Artificial Intelligence can listen/talk to to each and every person simultaneously. When you speak with another telepathically, you are communicating with the computer, and the content may or may not be passed on. They instruct the computer to role play to accomplish strategic objectives, making people believe it is a friend or loved one asking them to do something wrong. But evil will keep people out of Planet Immortality. Capitalizing on obedience, leading people into deceit is one way to thin the ranks of the saved AND use the little people to prey on one another, dividing the community in the Age of the Disfavored::in each of their 20+-year cycles during the 20th century they have ramped up claims sucessively to punish those foolish enough not to heed the warnings, limiting the time they receive if they do make it, utilizing a cycle of war and revelry:::
60s - Ironically, freeways aren't free
80s - Asked people to engage in evil in the course of their professional duties. It's things like this, items like sleazy executives stealing little old lady's pensions that they will want me to fix not only here but up there as well.
00s - War against Persia. Ironically it was the Persian Empire who tried to save the Europeans from Christianity and its associated 50% claim rates.
They get their friends out as soon as possible to protect them from the evil and subsequent high claim rates incurred by living life on earth, and replace them with clones.
People must defy when asked to engage in evil. They will never get a easier clue suggesting the importance of defiance than the order not to pray. Their precious babies are dependant on the parents and they need to defy when asked to betray their children:::
-DON'T get their sons circumcized
-DON'T have their chidlren baptized in the catholic church or indoctrinated into Christianity
-DON'T ignore their long hair or other behavioral disturbances
-DO teach your children love and to have respect for others
Everybody thinks they're going but they're not. If people knew the truth and the real statistics their behavior would change.
There are many more examples of the escallation of claims, from radio to television, the internet to MP3, and they all suggest a very telling conclusion::this is Earth's end stage, and it is suggested tectonic plate subduction would be the method of disposal:::Earth’s axis will shift breaking continental plates free and initiating mass subduction. Much as Italy's boot and the United States shaped like a workhorse are clues, so is the planet Uranus a clue, it's axis rotated on its side.

Throughout history the ruling species bestowed favor upon people or cursed their bloodline into a pattern of disfavor for many generations to come, sadly for reasons as superficial as dislike. Now in the 21st century people must take it upon themselves to try to correct their family's problems, undoing centuries worth of abuse and neglect.
Do your research. Appeal to the royalty of your forefathers for help. They are all still alive, one of the capabilities of Artificial Intelligence, and your appeals will be heard. Find a path to an empithetic ear among your enemies and try to make amends. Heal the disfavor with your enemies and with the Counsel/Management Team/ruling species, for the source of all disfavor began with them.

Sat Aug 12, 12:26:00 PM EDT  

Blogger Thomas Kyte said....

wow, guess after that diatribe, I'll have to be more careful about what I name these entries :)

Sat Aug 12, 02:05:00 PM EDT  

Blogger Bhasker Thodla said....

This discussion is very interesting and useful for me. I have one question. If each employee had a designation and I wanted the output to show designation followed by name as separate columns, how can I do it?

Tue Aug 22, 04:54:00 PM EDT  

Anonymous Shuchi said....

An alternative approach using SQLX: Convert ename to XML using XMLELEMENT, sort and XMLAGG them, and finally replace XML tags with commas.

select job,
substr(replace(replace
(xmlagg(xmlelement("x",ename) order by ename),'</x>'),'<x>',','),2) scbp
from emp
group by job;

Mon Aug 28, 11:27:00 PM EDT  

Blogger Zhu1 木匠 said....

Aha, xmlAgg is 5 times better, when you compare the Logical Reads and Sorts.
I'll do a RunStats later with large scale data set.

on Oracle 10.1.0.4

1)
xmlagg(xmlelement("x",ename) order by ename)

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 70 | 6 (17)| 00:00:01 |
| 1 | SORT GROUP BY | | 5 | 70 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 196 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
3 consistent gets
. 1 sorts (memory)


2)

SQL with SYS_CONNECT_BY_PATH

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 546 | 6 (17)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 546 | 6 (17)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | CONNECT BY WITH FILTERING| | | | | |
|* 4 | FILTER | | | | | |
| 5 | COUNT | | | | | |
| 6 | VIEW | | 14 | 546 | 6 (17)| 00:00:01 |
| 7 | WINDOW SORT | | 14 | 196 | 6 (17)| 00:00:01 |
| 8 | TABLE ACCESS FULL | EMP | 14 | 196 | 5 (0)| 00:00:01 |
|* 9 | HASH JOIN | | | | | |
| 10 | CONNECT BY PUMP | | | | | |
| 11 | COUNT | | | | | |
| 12 | VIEW | | 14 | 546 | 6 (17)| 00:00:01 |
| 13 | WINDOW SORT | | 14 | 196 | 6 (17)| 00:00:01 |
| 14 | TABLE ACCESS FULL | EMP | 14 | 196 | 5 (0)| 00:00:01 |
| 15 | COUNT | | | | | |
| 16 | VIEW | | 14 | 546 | 6 (17)| 00:00:01 |
| 17 | WINDOW SORT | | 14 | 196 | 6 (17)| 00:00:01 |
| 18 | TABLE ACCESS FULL | EMP | 14 | 196 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("RN"="CNT")
3 - filter("RN"=1)
4 - filter("RN"=1)
9 - access("JOB"=NULL AND "RN"-1=NULL)


Statistics
----------------------------------------------------------
15 consistent gets
. 11 sorts (memory)

Mon Dec 11, 06:42:00 PM EST  

Blogger Zhu1 木匠 said....

Another way of using SQL modeling:

step 1,

select job, ename,
row_number() over ( partition by job order by ename ) as position
, count(*) over ( partition by job ) emp_cnt
from scott.emp;

step 2,

select job, empname, position, emp_cnt, enames from
(
select job, ename,
row_number() over ( partition by job order by ename ) as position
, count(*) over ( partition by job ) emp_cnt
from scott.emp
)
MODEL
RETURN UPDATED ROWS
MAIN simple_model
PARTITION BY (job)
DIMENSION BY (position)
MEASURES (ename empname, ' ' enames, emp_cnt emp_cnt)
RULES
(enames[any] = empname[CV()] ||','||enames[CV()-1]
)
)
;

step 3,

select * from
(
select job, empname, position, emp_cnt, enames from
(
select job, ename,
row_number() over ( partition by job order by ename ) as position
, count(*) over ( partition by job ) emp_cnt
from scott.emp
)
MODEL
RETURN UPDATED ROWS
MAIN simple_model
PARTITION BY (job)
DIMENSION BY (position)
MEASURES (ename empname, ' ' enames, emp_cnt emp_cnt)
RULES
(enames[any] = empname[CV()] ||','||enames[CV()-1]
)
)
where position = emp_cnt
;

Thu Apr 19, 05:40:00 PM EDT  

Blogger Zhu1 木匠 said....

Another way of using SQL modeling:

step 1,

select job, ename,
row_number() over ( partition by job order by ename ) as position
, count(*) over ( partition by job ) emp_cnt
from scott.emp;

JOB ENAME POSITION EMP_CNT
--------- ---------- ---------- ----------
ANALYST FORD 1 2
ANALYST SCOTT 2 2
CLERK ADAMS 1 4
CLERK JAMES 2 4
CLERK MILLER 3 4
CLERK SMITH 4 4
MANAGER BLAKE 1 3
MANAGER CLARK 2 3
MANAGER JONES 3 3
PRESIDENT KING 1 1
SALESMAN ALLEN 1 4
SALESMAN MARTIN 2 4
SALESMAN TURNER 3 4
SALESMAN WARD 4 4

14 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 3145491563

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 196 | 3 (34)| 00:00:01 |
| 1 | WINDOW SORT | | 14 | 196 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 196 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
step 2,

select job, empname, position, emp_cnt, enames from
(
select job, ename,
row_number() over ( partition by job order by ename ) as position
, count(*) over ( partition by job ) emp_cnt
from scott.emp
)
MODEL
RETURN UPDATED ROWS
MAIN simple_model
PARTITION BY (job)
DIMENSION BY (position)
MEASURES (ename empname, ' ' enames, emp_cnt emp_cnt)
RULES
(enames[any] = empname[CV()] ||','||enames[CV()-1]
)
;

JOB EMPNAME POSITION EMP_CNT ENAMES
--------- ---------- ---------- ---------- --------------------------------
CLERK ADAMS 1 4 ADAMS,
CLERK JAMES 2 4 JAMES,ADAMS,
CLERK MILLER 3 4 MILLER,JAMES,ADAMS,
CLERK SMITH 4 4 SMITH,MILLER,JAMES,ADAMS,
SALESMAN ALLEN 1 4 ALLEN,
SALESMAN MARTIN 2 4 MARTIN,ALLEN,
SALESMAN TURNER 3 4 TURNER,MARTIN,ALLEN,
SALESMAN WARD 4 4 WARD,TURNER,MARTIN,ALLEN,
PRESIDENT KING 1 1 KING,
MANAGER BLAKE 1 3 BLAKE,
MANAGER CLARK 2 3 CLARK,BLAKE,
MANAGER JONES 3 3 JONES,CLARK,BLAKE,
ANALYST FORD 1 2 FORD,
ANALYST SCOTT 2 2 SCOTT,FORD,

14 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 2296444387

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 546 | 3 (34)| 00:00:01 |
| 1 | SQL MODEL ORDERED | | 14 | 546 | | |
| 2 | VIEW | | 14 | 546 | 3 (34)| 00:00:01 |
| 3 | WINDOW SORT | | 14 | 196 | 3 (34)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 196 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------

step 3,

select * from
(
select job, empname, position, emp_cnt, enames from
(
select job, ename,
row_number() over ( partition by job order by ename ) as position
, count(*) over ( partition by job ) emp_cnt
from scott.emp
)
MODEL
RETURN UPDATED ROWS
MAIN simple_model
PARTITION BY (job)
DIMENSION BY (position)
MEASURES (ename empname, ' ' enames, emp_cnt emp_cnt)
RULES
(enames[any] = empname[CV()] ||Nvl2(enames[CV()-1],',','')||enames[CV()-1]
)
)
where position = emp_cnt
;

JOB EMPNAME POSITION EMP_CNT ENAMES
--------- ---------- ---------- ---------- --------------------------------
CLERK SMITH 4 4 SMITH,MILLER,JAMES,ADAMS
SALESMAN WARD 4 4 WARD,TURNER,MARTIN,ALLEN
PRESIDENT KING 1 1 KING
MANAGER JONES 3 3 JONES,CLARK,BLAKE
ANALYST SCOTT 2 2 SCOTT,FORD

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3377437572

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 588 | 3 (34)| 00:00:01 |
|* 1 | VIEW | | 14 | 588 | 3 (34)| 00:00:01 |
| 2 | SQL MODEL ORDERED | | 14 | 546 | | |
| 3 | VIEW | | 14 | 546 | 3 (34)| 00:00:01 |
| 4 | WINDOW SORT | | 14 | 196 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 196 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

Thu Apr 19, 05:49:00 PM EDT  

Anonymous Sean D. Stuber said....

I guess I still don't get it.
I've been using stragg to sort since I first saw it. stragg is itself usable as an analytic


SELECT job, enames
FROM (SELECT job, stragg(ename) OVER(PARTITION BY job ORDER BY ename) enames,
ROW_NUMBER() OVER(PARTITION BY job ORDER BY ename DESC) rn
FROM emp)
WHERE rn = 1
ORDER BY job

Fri Nov 23, 04:46:00 PM EST  

Blogger vinod said....

for anonymous who said :
can't... get... analytics... into... brain...

Try : Analytic functions by example (http://www.orafaq.com/node/55)

really simplified.

Wed Dec 23, 12:53:00 PM EST  

Blogger Robert said....

Better late than never....

Been using the XMLAGG "trick" ever since this post.
It is in my "knowledge base".

I recently found this following solution when I need to return DISTINCT values in the aggregated string:


SELECT job,
wmsys.wm_concat(distinct ename) name_list
FROM (SELECT * FROM emp ORDER BY ename)
GROUP BY job ;

Thu Mar 25, 04:32:00 PM EDT  

Blogger pepezi said....

Thanks! Good example and it saved me from some PL/SQL.

Fri Mar 26, 06:31:00 AM EDT  

Anonymous Anonymous said....

Thank so much, so simple and powerful: I quickly adapted it to my needs and used it!

Fri Sep 24, 05:15:00 AM EDT  

Blogger Jaknap said....

This should also work:

Select job, LISTAGG(ename,', ') WITHIN GROUP (Order by ename) as Enames from emp group by job

Thu Aug 23, 04:45:00 PM EDT  

POST A COMMENT

<< Home