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.dizdare...@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 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 <gfranc...@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 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.dizdare...@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 <gfranc...@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: gfranc...@gmail.com >> > > -- George Francis e-mail: gfranc...@gmail.com
