There is a table say t1

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

CodePudding user response：

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;

CodePudding user response：

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

CodePudding user response：

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:

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:

db<>fiddle here