Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 202241337B67 for ; Wed, 12 Jan 2011 19:05:48 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 29836-08 for ; Wed, 12 Jan 2011 23:05:40 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ww0-f50.google.com (mail-ww0-f50.google.com [74.125.82.50]) by mail.postgresql.org (Postfix) with ESMTP id CA49C1337B49 for ; Wed, 12 Jan 2011 19:05:39 -0400 (AST) Received: by wwf26 with SMTP id 26so1167954wwf.19 for ; Wed, 12 Jan 2011 15:05:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=v4o1xUgk29HUJLLn+N8h80h6LgcrWSmnrJrvNiJX9qY=; b=LhxMTdm42dNY8jU3HqymAsbH4XwJkkD6UGxq+mVAZ9lpXcphPWruwnvUJNJQqeMjVE HZunf5v2gEc/MIciRax85ZZRYVlrNCx9gTVlUPKy/LSca1u/gymj3hvNINE//8XXdH3V lkDOFC+0mhY8k34ljB59yY/J0zPuF6Pn+bdBw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=kf/xBKXD/RtkMSSc4OcCtPNNskIZin/lyGrk/IuFBnSZ8X+ZwE4pFnls81rkm6fzIR KGIsbY8jt0c1HiYNWWXfoPb2spfWDD6lDlvce/U9RkSWPZG9WGE5mi8sbYJ5xUOkhI2C fnzT7WFflpCnFjjnb8xu3bX/0MnDtumpQ054Q= MIME-Version: 1.0 Received: by 10.216.141.75 with SMTP id f53mr1396962wej.16.1294873538966; Wed, 12 Jan 2011 15:05:38 -0800 (PST) Received: by 10.216.38.207 with HTTP; Wed, 12 Jan 2011 15:05:38 -0800 (PST) In-Reply-To: References: Date: Thu, 13 Jan 2011 00:05:38 +0100 Message-ID: Subject: Re: joining one record according to max value From: Jasmin Dizdarevic To: George Francis Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016e6de03be51b5c30499ae3d7e X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.668 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, FREEMAIL_REPLY=1.929, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7, RFC_ABUSE_POST=0.001 X-Spam-Level: X-Archive-Number: 201101/37 X-Sequence-Number: 34984 --0016e6de03be51b5c30499ae3d7e Content-Type: text/plain; charset=ISO-8859-1 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 > 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 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 >> >>> 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.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 >>>> >>>> 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 > --0016e6de03be51b5c30499ae3d7e Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable There is probably a more elegant way, but this could help you.

select i.source_id, nm.name, i.m= xscore from (
select sl.source_id, max(score) mxscore =A0from source s
inner= join sourcelevel sl on s.source_id =3D sl.source_id
inner join level l on= sl.level_id =3D l.level_id
group= by 1
) i
inner join (
select name, max(score) as mxs f= rom level
group= by 1
) nm on i.mxscore =3D nm.mxs


<= /div>
2011/1/12 George Francis <gfrancis1@gmail.com><= /span>
Close! =A0but 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&#= 39;m wrong. Is this, what you like to have?

select = sl.source_id, max(score) =A0from source s
inner join sourcelevel sl on s.source_id =3D sl.source_id
inner join level l on sl.level_id =3D l.level_id
group by 1<= /div>

This is the highest score per source_id.

2011/1/12 George Francis <gfrancis1@gmail.com>
hmm, but if I try to constrain the inner que= ry 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.l= evel_id =3D level.level_id and sourcelevel.source_id =3D source.source_id o= rder by score desc limit 1 )=A0
as temp on temp.source_id= =3D source.source_id;

ERROR:=A0 invalid reference to FROM-clause entry for table "source&= quot;

LINE 14: ...l_id =3D level.level_id and sourcelevel.source_id =3D source= .sou...

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ^

HINT:=A0 There is an entry for table "source", but it cannot b= e referenced from this part of the query.



********** Error **********


ERROR: invalid reference to FROM-clause entry for table "source&quo= t;

SQL state: 42P01

Hint: There is an entry for table "source", but it cannot be r= eferenced 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 <<= a href=3D"mailto:gfrancis1@gmail.com" target=3D"_blank">gfrancis1@gmail.com= >

The following sql is intended to produce =A0a 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&= #39; ), ( 3, 15, 'kappa' );

insert into sourcelevel =A0values ( 1, 1 ), ( 1,2 ), ( 3,3 );


select source.source_id, score, name from source

left join ( select * from sourcelevel, level where sourcelevel.level_id = =3D level.level_id order by score desc limit 1 )=A0

as temp on temp.source_id =3D source.source_id;


-- =
George




--
George Fran= cis
e-mail: gfr= ancis1@gmail.com




--
George Francis
e-mai= l: gfrancis1@gmail= .com

--0016e6de03be51b5c30499ae3d7e--