Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table student (id integer, firstname varchar(100), lastname varchar(100), time varchar(100));
        insert into student (id, firstname, lastname, time) values (1, 'albert', 'einstein', 'today');
        insert into student (id, firstname, lastname, time) values (2, 'isaac', 'newton', 'today');
        insert into student (id, firstname, lastname, time) values (3, 'marie', 'curie', 'today');
        insert into student (id, firstname, lastname, time) values (4, 'Aneesh', 'pn', 'yesterday');
        insert into student (id, firstname, lastname, time) values (5, 'joe', 'curie', 'yesterday');
        insert into student (id, firstname, lastname, time) values (6, 'Aneesh', 'hahha', 'yesterday');
        insert into student (id, firstname, lastname, time) values (7, 'Aneesh', 'narayanan', 'today');
        select * from student;

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1




id
firstname
lastname
time


1
albert
einstein
today

2
isaac
newton
today

3
marie
curie
today

4
Aneesh
pn
yesterday

5
joe
curie
yesterday

6
Aneesh
hahha
yesterday

7
Aneesh
narayanan
today



SELECT 7


select *
from student


id
firstname
lastname
time


1
albert
einstein
today

2
isaac
newton
today

3
marie
curie
today

4
Aneesh
pn
yesterday

5
joe
curie
yesterday

6
Aneesh
hahha
yesterday

7
Aneesh
narayanan
today



SELECT 7


select  s.*
from student s
inner join (
  select firstname, time
  from student
  where time= 'yesterday'
  order by id
  limit 1
) as f on f.time = s.time or f.firstname = s.firstname


id
firstname
lastname
time


4
Aneesh
pn
yesterday

5
joe
curie
yesterday

6
Aneesh
hahha
yesterday

7
Aneesh
narayanan
today



SELECT 4

id	firstname	lastname	time
1	albert	einstein	today
2	isaac	newton	today
3	marie	curie	today
4	Aneesh	pn	yesterday
5	joe	curie	yesterday
6	Aneesh	hahha	yesterday
7	Aneesh	narayanan	today