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

Question

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

Answer 1

use not exists and row_number() but for postgre it is wise to use distinct on which already given a answer by @Tim

 with cte as 
(  select t1.*,
  row_number()over(partition by subject order by id)
  rn from table_name t1
    where not exists ( select 1 from table_name t2 where t1.subject=t2.subject
                                and t2.event='test')  
) select * from cte where rn=1

Answer 2

You can use either NOT IN or not exists to exclude subjects for which event='test' exists. Using order Limit 1 with order by id will select one row with lowest value of id column.

Schema and insert statements:

 create table t1 (id    int,subject varchar(20), day varchar(20), event varchar(20));

 insert into t1 values(211, 'maths' ,'mon', 'asmnt');
 insert into t1 values(222, 'maths' ,'tue', 'asmnt');
 insert into t1 values(223, 'science'   ,'mon', 'test');
 insert into t1 values(224, 'science'   ,'tue', 'asmt');
 insert into t1 values(225, 'science'   ,'wed', 'insp');
 insert into t1 values(226, 'computers' ,'mon', 'asmt');
 insert into t1 values(227, 'maths' ,'mon', 'test');
 insert into t1 values(228, 'computers' ,'mon', 'insp');
 insert into t1 values(229, 'computers' ,'thr', 'asmnt');
 insert into t1 values(230, 'computers' ,'fri', 'asmnt');

Query1: using NOT IN

 SELECT * FROM t1 a
 WHERE subject not in
                 (
                   SELECT subject FROM t1 b WHERE b.event='test'
                 )
 order by id
 Limit 1

Output:

id	subject	day	event
226	computers	mon	asmt

Query2: using not exists

 SELECT * FROM t1 a
 WHERE not exists 
                 (
                   SELECT 1 FROM t1 b WHERE b.event='test' and a.subject=b.subject
                 )
 order by id
 Limit 1

Output:

id	subject	day	event
226	computers	mon	asmt

db<>fiddle here