Re: join problem or maybe group :( - Mailing list pgsql-general

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