From: | George Francis <gfrancis1(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | joining one record according to max value |
Date: | 2011-01-12 22:30:25 |
Message-ID: | AANLkTik6-BWRrRjmSCym3y4tmYkg+QsuqS-dxBti9Cqr@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
The following sql is intended to produce a list of all unique SOURCEs,
along with the corresponding LEVEL.NAME for LEVEL with highest SCORE value
via SOURCELEVEL.
I'd like to know why it doesn't return a SCORE value for SOURCE with id 3,
despite there being a SOURCELEVEL entry for it.
Many thanks,
drop table if exists source;
drop table if exists sourcelevel;
drop table if exists level;
create table source ( source_id int );
create table sourcelevel ( source_id int, level_id int);
create table level ( level_id int, score int, name text );
insert into source values (1), (2), (3);
insert into level values ( 1, 10 ,'alpha' ), ( 2, 20, 'beta' ), ( 3, 15,
'kappa' );
insert into sourcelevel values ( 1, 1 ), ( 1,2 ), ( 3,3 );
select source.source_id, score, name from source
left join ( select * from sourcelevel, level where sourcelevel.level_id =
level.level_id order by score desc limit 1 )
as temp on temp.source_id = source.source_id;
--
George
From | Date | Subject | |
---|---|---|---|
Next Message | George Francis | 2011-01-12 22:39:08 | Re: joining one record according to max value |
Previous Message | Protasov Vladimir | 2011-01-12 12:41:13 | INSERT/UPDATE .. RETURNING |