Search code examples
sqlpostgresqllimit

check if a specific column value exists corresponding to another column and return its id in sql

There is a table say t1

id subject day event
211 maths mon asmnt
222 maths tue asmnt
223 science mon test
224 science tue asmt
225 science wed insp
226 computers mon asmt
227 maths mon test
228 computers mon insp
229 computers thr asmnt
230 computers fri asmnt

now i want a SQL query to find id subject which never had event = test on any day and return its first event so output is

id subject day event
226 computers mon asmt
Solution

  • We can use DISTINCT ON here along with exists logic:

    SELECT DISTINCT ON (subject) *
FROM t1 a
WHERE NOT EXISTS (SELECT 1 FROM t1 b
                  WHERE b.subject = a.subject AND b.event = 'test')
ORDER BY subject, id;