Re: join problem or maybe group :(

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>