pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedjoining one record according to max value
6+ messages / 2 participants
[nested] [flat]
* joining one record according to max value
@ 2011-01-12 22:30 George Francis <gfrancis1@gmail.com>
2011-01-12 22:39 ` Re: joining one record according to max value George Francis <gfrancis1@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: George Francis @ 2011-01-12 22:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: joining one record according to max value
2011-01-12 22:30 joining one record according to max value George Francis <gfrancis1@gmail.com>
@ 2011-01-12 22:39 ` George Francis <gfrancis1@gmail.com>
2011-01-12 22:49 ` Re: joining one record according to max value Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: George Francis @ 2011-01-12 22:39 UTC (permalink / raw)
To: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: joining one record according to max value
2011-01-12 22:30 joining one record according to max value George Francis <gfrancis1@gmail.com>
2011-01-12 22:39 ` Re: joining one record according to max value George Francis <gfrancis1@gmail.com>
@ 2011-01-12 22:49 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-01-12 22:53 ` Re: joining one record according to max value George Francis <gfrancis1@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jasmin Dizdarevic @ 2011-01-12 22:49 UTC (permalink / raw)
To: George Francis <gfrancis1@gmail.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: joining one record according to max value
2011-01-12 22:30 joining one record according to max value George Francis <gfrancis1@gmail.com>
2011-01-12 22:39 ` Re: joining one record according to max value George Francis <gfrancis1@gmail.com>
2011-01-12 22:49 ` Re: joining one record according to max value Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
@ 2011-01-12 22:53 ` George Francis <gfrancis1@gmail.com>
2011-01-12 23:05 ` Re: joining one record according to max value Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: George Francis @ 2011-01-12 22:53 UTC (permalink / raw)
To: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: joining one record according to max value
2011-01-12 22:30 joining one record according to max value George Francis <gfrancis1@gmail.com>
2011-01-12 22:39 ` Re: joining one record according to max value George Francis <gfrancis1@gmail.com>
2011-01-12 22:49 ` Re: joining one record according to max value Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-01-12 22:53 ` Re: joining one record according to max value George Francis <gfrancis1@gmail.com>
@ 2011-01-12 23:05 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-01-12 23:38 ` Re: joining one record according to max value George Francis <gfrancis1@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jasmin Dizdarevic @ 2011-01-12 23:05 UTC (permalink / raw)
To: George Francis <gfrancis1@gmail.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: joining one record according to max value
2011-01-12 22:30 joining one record according to max value George Francis <gfrancis1@gmail.com>
2011-01-12 22:39 ` Re: joining one record according to max value George Francis <gfrancis1@gmail.com>
2011-01-12 22:49 ` Re: joining one record according to max value Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-01-12 22:53 ` Re: joining one record according to max value George Francis <gfrancis1@gmail.com>
2011-01-12 23:05 ` Re: joining one record according to max value Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
@ 2011-01-12 23:38 ` George Francis <gfrancis1@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: George Francis @ 2011-01-12 23:38 UTC (permalink / raw)
To: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>; +Cc: pgsql-sql
thanks! I think this is the best answer I will get.
On Wed, Jan 12, 2011 at 6:05 PM, Jasmin Dizdarevic <
jasmin.dizdarevic@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 <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 <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: gfrancis1@gmail.com
>>>>
>>>
>>>
>>
>>
>> --
>> George Francis
>> e-mail: gfrancis1@gmail.com
>>
>
>
--
George Francis
e-mail: gfrancis1@gmail.com
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-01-12 23:38 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-12 22:30 joining one record according to max value George Francis <gfrancis1@gmail.com>
2011-01-12 22:39 ` George Francis <gfrancis1@gmail.com>
2011-01-12 22:49 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-01-12 22:53 ` George Francis <gfrancis1@gmail.com>
2011-01-12 23:05 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-01-12 23:38 ` George Francis <gfrancis1@gmail.com>
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