thanks! I think this is the best answer I will get. On Wed, Jan 12, 2011 at 6:05 PM, Jasmin Dizdarevic < jasmin.dizdare...@gmail.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 <gfranc...@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.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 <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: gfranc...@gmail.com >>>> >>> >>> >> >> >> -- >> George Francis >> e-mail: gfranc...@gmail.com >> > > -- George Francis e-mail: gfranc...@gmail.com
