Re: joining one record according to max value

It's currently late, so excuse me if I'm wrong. Is this, what you like to
have?

select sl.source_id, max(score) from source s
inner join sourcelevel sl on s.source_id = sl.source_id
inner join level l on sl.level_id = l.level_id
group by 1

This is the highest score per source_id.

2011/1/12 George Francis <gfrancis1(at)gmail(dot)com>

> 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(dot)dizdarevic(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)com
>