Postgresql different where based on column value?

Question

Is it possible to dynamically change the where clause as a query executes based on the value of one of the columns? That is, lets say (and this is a completely made up example) I have a table of students, classes attended, and if they were tardy. I want to see for each student a list of all classes they have attended since the last time they were tardy. So the query would look something like this:

SELECT student, class, classdate
FROM attendance
WHERE classdate>(<<SOME QUERY that selects the most recent tardy for each student>>)
ORDER BY student,classdate;

or, to put it into more programing terminology, to perhaps make it clearer:

for studentName in (SELECT distinct(student) FROM attendance):
    SELECT student, class, classdate
    FROM attendance
    WHERE classdate>(SELECT classdate 
                     FROM attendance 
                     WHERE tardy=true AND student=studentName
                     ORDER BY classdate DESC
                     LIMIT 1)
    ORDER BY classdate;

Is there any way to do this with a single query, or do I need to do a separate query for each student (essentially as per the loop above)? The actual use case is more complicated to explain (it has to do with certifying records, and which ones need to be looked at) but conceptually it is the same.

oxc · Accepted Answer · 2012-06-30 19:48:43Z

1

Just use multiple aliases (e.g. a1 and a2) for the attendance table, so you can refer to the "outer" table alias in the subquery:

SELECT student, class, classdate
FROM attendance a1
WHERE classdate>(SELECT classdate 
                 FROM attendance a2
                 WHERE tardy=true AND a2.student=a1.student
                 ORDER BY classdate DESC
                 LIMIT 1)
ORDER BY classdate;

answered Jun 30, 2012 at 19:48

oxc

6873 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql different where based on column value?

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