LISTAGG with DISTINCT (Oracle)


Link to this posting

Postby 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? :twisted: 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);
User avatar
Ursego
Site Admin
 
Posts: 143
Joined: 19 Feb 2013, 20:33



Ketones are a more high-octane fuel for your brain than glucose. Become a biohacker and upgrade yourself to version 2.0!



cron
Traffic Counter

eXTReMe Tracker