4 Replies Latest reply: Feb 28, 2013 6:28 AM by Mark T. RSS

    Returning the first row with a non-null value in a column

    Mark T.
      Hey there, gurus. Can someone give me a hand with this?

      I have a table with six columns: Class_ID, Course_ID, Start_Date, Last_Name, First_Name, Company. When our students take a class and fill out an evaluation form, they fill in the Class_ID, Last_Name, First_Name, and Company. The Course_ID and Start_Date are automatically populated based on the Class_ID.

      I'm trying to generate a report that contains Class_ID, Course_ID, and Start_Date, PLUS the first non-null value in the Company column for a given combination of the first three columns. Here's my SQL that doesn't quite do the trick:

      select distinct c.class_id, c.course_id, c.start_date, p.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
      order by class_id, start_date, company, course_id

      Right now, if in a given class one student entered 'CCCC' as the company name, and other entered 'Collin Co Comm Coll', and another entered 'Collin College', I would get three rows for that class when using the SQL above. I just want one row. I don't really care which of the company names gets selected - any of them will be fine for our purposes. I just need to see one row for each class_id, and one value that some student typed into the Company field. And I suppose I should say that if no student fills in the company name I need to see the null, because I need to see the row no matter what.

      Sounds like some kind of funky outer join or a coalesce, but it's beyond my ability to figure it out.

      Right now I get this:
      Class 1234 Course SQL271 Date 07/22/2013 Company XYZ Inc.
      Class 1234 Course SQL271 Date 07/22/2013 Company XYZ Incorporated
      Class 1234 Course SQL271 Date 07/22/2013 Company XYZ

      What I want:
      Class 1234 Course SQL271 Date 07/22/2013 Company XYZ Inc. or either of the other 2

      Thanks in advance!!
        • 1. Re: Returning the first row with a non-null value in a column
          jeneesh
          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
          Or if you want to list all the companies, you can use LISTAGG if you are on the latest version of ORACLE
          • 2. Re: Returning the first row with a non-null value in a column
            Karthick_Arp
            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
            • 3. Re: Returning the first row with a non-null value in a column
              Mark T.
              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!
              • 4. Re: Returning the first row with a non-null value in a column
                Mark T.
                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!!