PostgreSQL null out repeat results in result set

Question

I am returning a result set that joins across a schools, students, andgrades` tables. I am basically attempting to query for the grades of each student in each university (this is a toy example):

SELECT s.name, s.description, st.name, g.course, g.grade
FROM school s
JOIN students st ON st.school_id = s.id
JOIN grades g ON g.student_id = st.id

The most straightforward join query, basically.

However, for stylistic and formatting purposes, I'd like to be null out repeat (redundant) information. I struggled to articulate this to a colleague verbally, so I'm relying on this visual to explain what I have and what I want:

Notice how many of the cells for School and School Description are nulled out, because they are repeated. I vaguely remember using a PostgreSQL function (perhaps some WINDOW function? Some special form of GROUP BY? A PARTITION BY) a long time ago that did exactly this. However, I've googled around and scoured the PostgreSQL documentation but have yet to find it.

Does anyone know how to implement this in PostgreSQL? I remember it being relatively straightforward, requiring only a few syntax changes - nothing too verbose or intricate.

I'm sorry I can't be more descriptive! Yes, I know this can be done in the application layer. But I'm asking specifically for SQL options to perform this task.

This is best done in the application layer.

Gordon Linoff
– Gordon Linoff

2019-02-02 17:24:40 +00:00
Commented Feb 2, 2019 at 17:24 — Gordon Linoff
– Gordon Linoff, Commented Feb 2, 2019 at 17:24

Gordon Linoff · Accepted Answer · 2019-02-02 17:28:16Z

This is best done in the application layer. SQL tables and results sets are based on unordered sets. So, it is not "SQLish" to have the results in one row depend on values in "preceding" rows. For this reason, such formatting is more appropriate for the presentation layer than the database layer.

That said, you can do what you want, using window functions. It looks like this:

SELECT (CASE WHEN row_number() OVER (partition by s.name ORDER BY st.name, g.course) = 1
             THEN s.name
        END) as school_name,
       (CASE WHEN row_number() OVER (partition by s.name ORDER BY st.name, g.course) = 1
             THEN s.description
        END) as school_name,
       s.description,
       (CASE WHEN row_number() OVER (partition by s.name, st.name ORDER BY g.course = 1 )
             THEN st.name
        END) as student_name,
       g.course, g.grade
FROM school s JOIN
     students st
     ON st.school_id = s.id JOIN
     grades g
     ON g.student_id = st.id
ORDER BY s.name, st.name, g.course

Collectives™ on Stack Overflow

PostgreSQL null out repeat results in result set

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related