snippetsqlMinor
How to get max value of a column from one table with its matching record in second table in PostgreSQL?
Viewed 0 times
postgresqlcolumnwithvalueonegetmaxitsrecordhow
Problem
I have two tables as follows:
Now I insert some records in both tables:
Now I want to select
So my desired output would be:
Anybody please help.
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 | 4Anybody please help.
Solution
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 descCode Snippets
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 descContext
StackExchange Database Administrators Q#143827, answer score: 3
Revisions (0)
No revisions yet.