I have two tables as follows:
create table two(grade_id int, edu varchar(20),sortby int);
alter table two add constraint pk_one primary key(grade_id);
create table one(id int, name varchar(20),grade int);
alter table one add constraint fk_two_one foreign key(grade) references two(grade_id);
Now I insert some records in both tables:
insert into two values(1,'High School',1);
insert into two values(2,'Bachelor',3);
insert into two values(3,'College',2);
insert into two values(4,'Masters',4);
insert into two values(10,'PHD',5);
insert into one values (1,'Ahmad',10);
insert into one values (1,'Ahmad',3);
insert into one values (1,'Ahmad',4);
insert into one values (2,'Ghani',1);
insert into one values (2,'Ghani',3);
insert into one values (2,'Ghani',2);
insert into one values (3,'Fahim',4);
insert into one values (3,'Fahim',1);
insert into one values (3,'Fahim',3);
insert into one values (3,'Fahim',2);
Now I want to select id,name from table one with its edu and sortby columns from table two that has max value in sortby column.
So my desired output would be:
id + name + education + sortby
1 | Ahmad | PHD | 5
2 | Ghani | Bachelor| 3
3 | Fahim | Masters | 4
Anybody please help.
Best Answer
In Postgres the most efficient way is to do this using
distinct on ():