Re: joining one record according to max value
От | George Francis |
---|---|
Тема | Re: joining one record according to max value |
Дата | |
Msg-id | AANLkTikGmQphfEXcGxGx7LbbrwJGTW=Oi941O2sm=Fty@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: joining one record according to max value (Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>) |
Ответы |
Re: joining one record according to max value
(Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>)
|
Список | pgsql-sql |
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 sinner join sourcelevel sl on s.source_id = sl.source_idinner join level l on sl.level_id = l.level_idgroup by 1This is the highest score per source_id.2011/1/12 George Francis <gfrancis1@gmail.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 sourceleft 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
--
George Francis
e-mail: gfrancis1@gmail.com
В списке pgsql-sql по дате отправления: