Re: joining one record according to max value

hmm, but if I try to constrain the inner query to the source_id of the outer query I get an error as follows:

select source.source_id, score, name from source

left join ( select * from sourcelevel, level where sourcelevel.level_id = level.level_id and sourcelevel.source_id = source.source_id order by score desc limit 1 ) 

as temp on temp.source_id = source.source_id;

ERROR:  invalid reference to FROM-clause entry for table "source"

LINE 14: ...l_id = level.level_id and sourcelevel.source_id = source.sou...

                                                              ^

HINT:  There is an entry for table "source", but it cannot be referenced from this part of the query.

********** Error **********

ERROR: invalid reference to FROM-clause entry for table "source"

SQL state: 42P01

Hint: There is an entry for table "source", but it cannot be referenced from this part of the query.

Character: 601

On Wed, Jan 12, 2011 at 5:35 PM, Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com> wrote:

Because of the "score desc limit 1". The subselect returns only the higest score and this is level 2.

2011/1/12 George Francis <gfrancis1@gmail.com>

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

--
George Francis
e-mail: gfrancis1@gmail.com