This content has been marked as final. Show 4 replies
Or if you want to list all the companies, you can use LISTAGG if you are on the latest version of ORACLE
select c.class_id, c.course_id, c.start_date, max(p.company) company from class c, person p, student s where substr(c.course_id,1,3) = 'OBI' and c.start_date >= SYSDATE-30 and c.class_id = s.class_id and s.pid = p.pid group by c.class_id, c.course_id, c.start_date order by class_id, start_date, course_id
May be this
select c.class_id , c.course_id , c.start_date , p.company from class c join student s on c.class_id = s.class_id left join ( select pid , company from ( select pid, company, row_number() over(partition by pid order by company) rno from person p ) where rno = 1 ) on p.pid = s.pid where substr(c.course_id,1,3) = 'OBI' and c.start_date >= SYSDATE-30 order by class_id , start_date , company , course_id
Karthick, thank you for the reply. I'll have to award points to the other answer since it arrived earlier, is amazingly simple, and is correct. I appreciate the effort though. Thanks!
MAX!!! Of course! One day I will perhaps remember that MAX is not just a numeric function.
And yes, I knew that the very cool LISTAGG function would concatenate all of the submissions, but your answer is precisely what I wanted.
Full credit given. THANK YOU!!