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 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 >
