HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to get max value of a column from one table with its matching record in second table in PostgreSQL?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlcolumnwithvalueonegetmaxitsrecordhow

Problem

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.

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 desc

Code 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 desc

Context

StackExchange Database Administrators Q#143827, answer score: 3

Revisions (0)

No revisions yet.