Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 1D12A1337B61 for ; Wed, 12 Jan 2011 18:54:05 -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 24229-01 for ; Wed, 12 Jan 2011 22:53:57 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iy0-f174.google.com (mail-iy0-f174.google.com [209.85.210.174]) by mail.postgresql.org (Postfix) with ESMTP id CA8D11337B68 for ; Wed, 12 Jan 2011 18:53:56 -0400 (AST) Received: by iyj18 with SMTP id 18so976694iyj.19 for ; Wed, 12 Jan 2011 14:53:56 -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=UCbe/tFncKKhW7yxdNMHI6sqT2hwR4XQiIJRFwLig1w=; b=Zc3OImvOXIxhCvEilzNDEf3pUAVlqOpFw1q0ljK7nz/4cfHT5KmTdAiaOB/t06QFcc 0wxYENb/d5ZRJANwx5agMYV0XDOfFA1ArdI8X9jOqX325fNezIQ7cOVA9ELk5gGeJKRQ A9eUSAHUGqYB7QrjyIk1oHrYSltWTVPoFShD0= 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=XW364fkm9ejqXsPGTpoAjI/owacPOTpYLh3CFlPBiC+FPHu791il/vDKLyHo8dWpgi 8kM9WlLNKFGc/fqDSLGxzDM8AQVkQEY9u4Wp95BZV9tBYiZGSiVY1dojjrlxwW3g+eKH hzkLtdlypGGPVFY1bsfOFdnfewStMtdaVfeao= MIME-Version: 1.0 Received: by 10.42.172.130 with SMTP id n2mr1714311icz.246.1294872836183; Wed, 12 Jan 2011 14:53:56 -0800 (PST) Received: by 10.42.180.129 with HTTP; Wed, 12 Jan 2011 14:53:56 -0800 (PST) In-Reply-To: References: Date: Wed, 12 Jan 2011 17:53:56 -0500 Message-ID: Subject: Re: joining one record according to max value From: George Francis To: Jasmin Dizdarevic Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=90e6ba6e86726e17cc0499ae13c2 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.044 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=1.553, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7, RFC_ABUSE_POST=0.001 X-Spam-Level: X-Archive-Number: 201101/36 X-Sequence-Number: 34983 --90e6ba6e86726e17cc0499ae13c2 Content-Type: text/plain; charset=ISO-8859-1 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 --90e6ba6e86726e17cc0499ae13c2 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Close! =A0but I need the name of the LEVEL with highest score, and I dont t= hink I can get it this way.
Thanks for trying though!

On Wed, Jan 12, 2011 at 5:49 PM, Jasmin Dizdarevic <jasmin.dizdar= evic@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 Geor= ge 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
--90e6ba6e86726e17cc0499ae13c2--