3

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.

0
3

In Postgres the most efficient way is to do this using distinct on ():

select distinct on (o.id) o.id, o.name, t.edu, t.sortby
from one o 
  join two t on o.grade = t.grade_id
order by o.id, t.sortby desc  
1
  • 2
    I'd say the easiest to write, not necessarily the most efficient. Using the LATERAL more complicated syntax, often leads to more efficient plan. – ypercubeᵀᴹ Jul 14 '16 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.