Thread: join problem or maybe group :(

join problem or maybe group :(

From

"Ben-Nes Michael"

Date:

28 August 2002, 16:38:04

Hi All


I want to select sections using left join on articles, but i want to retrive
only one row from articles per section and the one should be the latest
art_date.

Can it be done ?

CREATE TABLE sections (
sec_id SERIAL PRIMARY KEY,
sec_name VARCHAR (30),
sec_order INT2
);


CREATE TABLE articles (
art_id SERIAL PRIMARY KEY,
sec_id INT4 REFERENCES sections ON UPDATE CASCADE ON DELETE CASCADE,
art_name VARCHAR (30),
art_date DATE
);

Cheer

Re: join problem or maybe group :(

From

"Ben-Nes Michael"

Date:

29 August 2002, 13:16:42

hmmm, me again.

Ill attack the subject from another way.

Can i limit the left joined table to one result ?

like:

select * from table1 left join table2 using (column) limit table2 1,0; ?

> Hi All
>
>
> I want to select sections using left join on articles, but i want to
retrive
> only one row from articles per section and the one should be the latest
> art_date.
>
> Can it be done ?
>
> CREATE TABLE sections (
> sec_id SERIAL PRIMARY KEY,
> sec_name VARCHAR (30),
> sec_order INT2
> );
>
>
> CREATE TABLE articles (
> art_id SERIAL PRIMARY KEY,
> sec_id INT4 REFERENCES sections ON UPDATE CASCADE ON DELETE CASCADE,
> art_name VARCHAR (30),
> art_date DATE
> );
>
> Cheer
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Re: join problem or maybe group :(

From

Stephan Szabo

Date:

29 August 2002, 13:26:18

On Thu, 29 Aug 2002, Ben-Nes Michael wrote:

> hmmm, me again.
>
> Ill attack the subject from another way.
>
> Can i limit the left joined table to one result ?
>
> like:
>
> select * from table1 left join table2 using (column) limit table2 1,0; ?

I don't think so, however, it's possible that maybe a distinct on in
a subselect may help if you don't mind using postgresql extensions.

Maybe something like (complete untested):

select * from sections left join
 (select distinct on (sec_id) * from articles order by art_date desc)
 as articles
 using (sec_id);

> > I want to select sections using left join on articles, but i want to
> retrive
> > only one row from articles per section and the one should be the latest
> > art_date.
> >
> > Can it be done ?
> >
> > CREATE TABLE sections (
> > sec_id SERIAL PRIMARY KEY,
> > sec_name VARCHAR (30),
> > sec_order INT2
> > );
> >
> >
> > CREATE TABLE articles (
> > art_id SERIAL PRIMARY KEY,
> > sec_id INT4 REFERENCES sections ON UPDATE CASCADE ON DELETE CASCADE,
> > art_name VARCHAR (30),
> > art_date DATE
> > );
> >
> > Cheer
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Re: join problem or maybe group :(

From

"Ben-Nes Michael"

Date:

29 August 2002, 14:35:51

found a way, but im not sure its the optimum way

SELECT * from sections left join articles using (sec_id) where art_id =
(SELECT art_id from articles where sec_id = sections.sec_id order by
art_date DESC limit 1,0) order by sec_order;

Just wonder, how can i do:

select max(column1), column2 from table;

its saying:
ERROR:  Attribute table.column2 must be GROUPed or used in an aggregate
function

and all i want is that column2 value will be the mate fo column1 max?

like if
clumn1     column2
1                 a
2                 b
3                 c

it will return 3,c
>
> On Thu, 29 Aug 2002, Ben-Nes Michael wrote:
>
> > hmmm, me again.
> >
> > Ill attack the subject from another way.
> >
> > Can i limit the left joined table to one result ?
> >
> > like:
> >
> > select * from table1 left join table2 using (column) limit table2 1,0; ?
>
> I don't think so, however, it's possible that maybe a distinct on in
> a subselect may help if you don't mind using postgresql extensions.
>
> Maybe something like (complete untested):
>
> select * from sections left join
>  (select distinct on (sec_id) * from articles order by art_date desc)
>  as articles
>  using (sec_id);
>
> > > I want to select sections using left join on articles, but i want to
> > retrive
> > > only one row from articles per section and the one should be the
latest
> > > art_date.
> > >
> > > Can it be done ?
> > >
> > > CREATE TABLE sections (
> > > sec_id SERIAL PRIMARY KEY,
> > > sec_name VARCHAR (30),
> > > sec_order INT2
> > > );
> > >
> > >
> > > CREATE TABLE articles (
> > > art_id SERIAL PRIMARY KEY,
> > > sec_id INT4 REFERENCES sections ON UPDATE CASCADE ON DELETE CASCADE,
> > > art_name VARCHAR (30),
> > > art_date DATE
> > > );
> > >
> > > Cheer
> > >
> > >
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: join problem or maybe group :(

From

Patrick FICHE

Date:

29 August 2002, 14:40:04

You syntax is not correct...
All columns in the select must be in an aggregate function or in the group
by clause.

You could try something like

select column1, column2 from table where column1 = ( select max( column1 )
from table )

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Ben-Nes Michael
Sent: Thursday, August 29, 2002 6:35 PM
To: Stephan Szabo
Cc: postgres
Subject: Re: [GENERAL] join problem or maybe group :(


found a way, but im not sure its the optimum way

SELECT * from sections left join articles using (sec_id) where art_id =
(SELECT art_id from articles where sec_id = sections.sec_id order by
art_date DESC limit 1,0) order by sec_order;

Just wonder, how can i do:

select max(column1), column2 from table;

its saying:
ERROR:  Attribute table.column2 must be GROUPed or used in an aggregate
function

and all i want is that column2 value will be the mate fo column1 max?

like if
clumn1     column2
1                 a
2                 b
3                 c

it will return 3,c
>
> On Thu, 29 Aug 2002, Ben-Nes Michael wrote:
>
> > hmmm, me again.
> >
> > Ill attack the subject from another way.
> >
> > Can i limit the left joined table to one result ?
> >
> > like:
> >
> > select * from table1 left join table2 using (column) limit table2 1,0; ?
>
> I don't think so, however, it's possible that maybe a distinct on in
> a subselect may help if you don't mind using postgresql extensions.
>
> Maybe something like (complete untested):
>
> select * from sections left join
>  (select distinct on (sec_id) * from articles order by art_date desc)
>  as articles
>  using (sec_id);
>
> > > I want to select sections using left join on articles, but i want to
> > retrive
> > > only one row from articles per section and the one should be the
latest
> > > art_date.
> > >
> > > Can it be done ?
> > >
> > > CREATE TABLE sections (
> > > sec_id SERIAL PRIMARY KEY,
> > > sec_name VARCHAR (30),
> > > sec_order INT2
> > > );
> > >
> > >
> > > CREATE TABLE articles (
> > > art_id SERIAL PRIMARY KEY,
> > > sec_id INT4 REFERENCES sections ON UPDATE CASCADE ON DELETE CASCADE,
> > > art_name VARCHAR (30),
> > > art_date DATE
> > > );
> > >
> > > Cheer
> > >
> > >
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: join problem or maybe group :(

From

Tom Lane

Date:

29 August 2002, 14:42:54

"Ben-Nes Michael" <miki@canaan.co.il> writes:
> Just wonder, how can i do:
> select max(column1), column2 from table;
> its saying:
> ERROR:  Attribute table.column2 must be GROUPed or used in an aggregate
> function
> and all i want is that column2 value will be the mate fo column1 max?

You want DISTINCT ON ... see the weather-reports example on the SELECT
man page.

            regards, tom lane

Re: join problem or maybe group :(

From

Diogo Biazus

Date:

29 August 2002, 16:44:17

I think that a subselect will do that, but I'm not sure if it is the
best way.
Try something like:

select max(column1), (select column2 from table where column1 = (select max(column1) from table)) from table;


>Just wonder, how can i do:
>
>select max(column1), column2 from table;
>
>its saying:
>ERROR:  Attribute table.column2 must be GROUPed or used in an aggregate
>function
>
>and all i want is that column2 value will be the mate fo column1 max?
>
>like if
>clumn1     column2
>1                 a
>2                 b
>3                 c
>
>it will return 3,c
>

--
Diogo de Oliveira Biazus
diogo@ikono.com.br
Ikono Sistemas e Automação
http://www.ikono.com.br

Re: join problem or maybe group :(

From

"Henshall, Stuart - WCP"

Date:

30 August 2002, 12:29:01

There is a non-ansi way of doing it:
SELECT column1,column2 FROM table ORDER BY column1 DESC LIMIT 1;
- Stuart

> -----Original Message-----
> From: Diogo Biazus [mailto:diogo@ikono.com.br]
>
>
> I think that a subselect will do that, but I'm not sure if it is the
> best way.
> Try something like:
>
> select max(column1), (select column2 from table where column1
> = (select max(column1) from table)) from table;
>
>
> >Just wonder, how can i do:
> >
> >select max(column1), column2 from table;
> >
> >its saying:
> >ERROR: Attribute table.column2 must be GROUPed or used in
> an aggregate
> >function
> >
> >and all i want is that column2 value will be the mate fo column1 max?
> >
> >like if
> >clumn1     column2
> >1                 a
> >2                 b
> >3                 c
> >
> >it will return 3,c
> >
>
> --
> Diogo de Oliveira Biazus
> diogo@ikono.com.br
> Ikono Sistemas e Automação
> http://www.ikono.com.br
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>