From: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
To: George Francis <gfrancis1@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: joining one record according to max value
Date: Thu, 13 Jan 2011 00:05:38 +0100
Message-ID: <AANLkTimguFqO_eHV06Jv7PSGAjyec96UEkxLCYVFktAa@mail.gmail.com> (raw)
In-Reply-To: <AANLkTikGmQphfEXcGxGx7LbbrwJGTW=Oi941O2sm=Fty@mail.gmail.com>
References: <AANLkTik6-BWRrRjmSCym3y4tmYkg+QsuqS-dxBti9Cqr@mail.gmail.com>
<AANLkTi=vELQyTFEB29Epkm_ZCPGLdQY+DT+cOZ3mL6VT@mail.gmail.com>
<AANLkTikb8OHdGEM74yMk=fuxGucm+4heY62N6CQa-hEm@mail.gmail.com>
<AANLkTi=sRZFOFBEo2bf75iNd0JDkv9md03hAzB95oUMo@mail.gmail.com>
<AANLkTikGmQphfEXcGxGx7LbbrwJGTW=Oi941O2sm=Fty@mail.gmail.com>
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 <gfrancis1@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.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 <gfrancis1@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.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 <gfrancis1@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: gfrancis1@gmail.com
>>>
>>
>>
>
>
> --
> George Francis
> e-mail: gfrancis1@gmail.com
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasmin.dizdarevic@gmail.com, gfrancis1@gmail.com
Subject: Re: joining one record according to max value
In-Reply-To: <AANLkTimguFqO_eHV06Jv7PSGAjyec96UEkxLCYVFktAa@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox