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)
>