LISTAGG with DISTINCT (Oracle)

[Ursego]

Link to this posting

by Ursego » 26 Nov 2023, 15:29

LISTAGG doesn't support DISTINCT. For example, you want a comma-separated list of students who are assigned to student groups:

Code: Select all

SELECT LISTAGG(g.student_id, ', ')
WITHIN GROUP (ORDER BY g.student_id)
  FROM student_group g;

Since each student can be assigned to many groups, he/she will appear in the list a few times. To see each student only once, you would want to write something like

Code: Select all

SELECT LISTAGG(DISTINCT g.student_id, ', ')...

I don't know why, but that syntax doesn't work. Larry Ellison, can you explain why? That is pretty standard requirement.

Ok, I have good news - you can mimic DISTINCT in LISTAGG using rowid. A little bit ugly, but working:

Code: Select all

SELECT LISTAGG(g.student_id, ', ')
WITHIN GROUP (ORDER BY g.student_id)
  FROM student_group g
 WHERE g.rowid = (SELECT MIN(g2.rowid) -- you can use MAX if you wish
                    FROM student_group g2
                   WHERE g2.student_id = g.student_id);