Re: joining one record according to max value

thanks! I think this is the best answer I will get.

On Wed, Jan 12, 2011 at 6:05 PM, Jasmin Dizdarevic <
jasmin(dot)dizdarevic(at)gmail(dot)com> wrote:

> There is probably a more elegant way, but this could help you.
>
> select i.source_id, nm.name, i.mxscore from (
> select sl.source_id, max(score) mxscore 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
> ) i
> inner join (
> select name, max(score) as mxs from level
> group by 1
> ) nm on i.mxscore = nm.mxs
>
>
>
> 2011/1/12 George Francis <gfrancis1(at)gmail(dot)com>
>
>> Close! but I need the name of the LEVEL with highest score, and I dont
>> think I can get it this way.
>> Thanks for trying though!
>>
>>
>> On Wed, Jan 12, 2011 at 5:49 PM, Jasmin Dizdarevic <
>> jasmin(dot)dizdarevic(at)gmail(dot)com> wrote:
>>
>>> 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 <http://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
>>>>
>>>
>>>
>>
>>
>> --
>> George Francis
>> e-mail: gfrancis1(at)gmail(dot)com
>>
>
>

--
George Francis
e-mail: gfrancis1(at)gmail(dot)com