Re: [HACKERS] is it possible to use LIMIT and INTERSECT ?

On Mon, 18 Oct 1999, Hannu Krosing wrote:

> Date: Mon, 18 Oct 1999 08:22:31 +0000
> From: Hannu Krosing <hannu@tm.ee>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] is it possible to use LIMIT and INTERSECT ?
> 
> Oleg Bartunov wrote:
> > 
> > Tom,
> > 
> > patch was applied smoothly to 6.5.2
> > What's the syntax ?
> > 
> > select  a.msg_id, c.status_set_date, c.title
> >         from Message_Keyword_map a, messages c, keywords d
> >         where c.status_id =1 and d.name ~* 'moon'  and a.key_id=d.key_id
> >         and c.msg_id=a.msg_id
> > intersect
> >         select  a.msg_id, a.status_set_date, a.title from messages a
> >             where a.status_id = 1 and a.title ~* 'moon' limit 5;
> > 
> > produces (10 rows)
> > 
> > select  a.msg_id, c.status_set_date, c.title
> >         from Message_Keyword_map a, messages c, keywords d
> >         where c.status_id =1 and d.name ~* 'moon'  and a.key_id=d.key_id
> >         and c.msg_id=a.msg_id limit 5
> > intersect
> >         select  a.msg_id, a.status_set_date, a.title from messages a
> >             where a.status_id = 1 and a.title ~* 'moon' limit 5;
> > 
> 
> As the limit is applied to the final result, I guess you can have only one 
> LIMIT per query.
> 
> So try removing the limit 5 before intersect .
> 

This was my first try (look above). It works but produces 10 rows instead of 5.
Oleg

> -----------
> Hannu
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83