From: | George Francis <gfrancis1(at)gmail(dot)com> |
---|---|
To: | Jasmin Dizdarevic <jasmin(dot)dizdarevic(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: joining one record according to max value |
Date: | 2011-01-12 22:39:08 |
Message-ID: | AANLkTikb8OHdGEM74yMk=fuxGucm+4heY62N6CQa-hEm@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jasmin Dizdarevic | 2011-01-12 22:49:30 | Re: joining one record according to max value |
Previous Message | George Francis | 2011-01-12 22:30:25 | joining one record according to max value |