Thread: joining one record according to max value
<p class="p1">The following sql is intended to produce a list of all unique SOURCEs, along with the corresponding <a href="http://LEVEL.NAME">LEVEL.NAME</a>for LEVEL with highest SCORE value via SOURCELEVEL.<p class="p1">I'd like to knowwhy it doesn't return a SCORE value for SOURCE with id 3, despite there being a SOURCELEVEL entry for it.<p class="p1">Manythanks,<p class="p2"><br /><p class="p3">drop table if exists source;<p class="p3">drop table if exists sourcelevel;<pclass="p3">drop table if exists level;<p class="p4"><br /><p class="p3">create table source ( source_id int);<p class="p3">create table sourcelevel ( source_id int, level_id int);<p class="p3">create table level ( level_id int,score int, name text );<p class="p4"><br /><p class="p3">insert into source values (1), (2), (3);<p class="p3">insertinto level values ( 1, 10 ,'alpha' ), ( 2, 20, 'beta' ), ( 3, 15, 'kappa' );<p class="p3">insert into sourcelevel values ( 1, 1 ), ( 1,2 ), ( 3,3 );<p class="p4"><br /><p class="p3">select source.source_id, score, name fromsource<p class="p3">left join ( select * from sourcelevel, level where sourcelevel.level_id = level.level_id order byscore desc limit 1 ) <p class="p3"><span class="Apple-tab-span"> </span>as temp on temp.source_id = source.source_id;<br/>-- <br />George<br />
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
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
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
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
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.dizdarevic@gmail.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 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
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 sinner join sourcelevel sl on s.source_id = sl.source_idinner join level l on sl.level_id = l.level_idgroup by 1) iinner join (select name, max(score) as mxs from levelgroup by 1) nm on i.mxscore = nm.mxs2011/1/12 George Francis <gfrancis1@gmail.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.dizdarevic@gmail.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 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
--
George Francis
e-mail: gfrancis1@gmail.com