Postgres Pro
Downloads
Home   >   mailing lists

Thread: problem selecting multiple records (again!)

problem selecting multiple records (again!)

From
"Giorgio Andreoletti - mentelocale"
Date:
hi everyone,
i already wrote to this list for the same problem.... i thought i had
resolved it, but i was wrong !
i'll explain my problem in a different (and simplier to understand) way:

suppose you have a table representing some events like this:

id  |  ref  |  date
-----------------------
1  |  10  |  2001-05-10
2  |  10  |  2001-05-11
3  |  10  |  2001-05-12
4  |  20  |  2001-05-11
5  |  20  |  2001-05-13
6  |  30  |  2001-05-15

the "ref" field is used to match this table with an another table containing
the description of the event in that date.
So, in this example I have the same event (identified as '10') which is
repeated 3 times, another event ('20') repeated twice, and an event with a
single occurance ('30').
now suppose that today is 2001-05-09 and i want to publish on my web-site
the next 3 events:

doing a simple SELECT i will get :

1  |  10  | 2001-05-10
2  |  10  |  2001-05-11
4  |  20  |  2001-05-11

but what i really wanted was :

1  |  10  | 2001-05-10
4  |  20  |  2001-05-11
6  |  30  |  2001-05-15

i.e., i don't want to get more than one occurance of the same "ref".

plz help me since i'm getting crazy trying to solve this problem !!!
keep in mind that i'll have a table with hundreds of event, so the faster
way to get the result is the preferred :-)

the web publishing system is based on zope.

tnx for your help

============================
Giorgio Andreoletti - mentelocale S.r.l.
responsabile hardware e networking
responsabile sviluppo web
www.mentelocale.it
giorgio.andreoletti@mentelocale.it
============================

Re: problem selecting multiple records (again!)

From
Manuel Sugawara
Date:
something like

select * from events where ref in (select distinct ref from events limit 3);

will do what you want.

hth,
Manuel.

"Giorgio Andreoletti - mentelocale" <giorgio.andreoletti@mentelocale.it> writes:

> hi everyone,
> i already wrote to this list for the same problem.... i thought i had
> resolved it, but i was wrong !
> i'll explain my problem in a different (and simplier to understand) way:
>
> suppose you have a table representing some events like this:
>
> id  |  ref  |  date
> -----------------------
> 1  |  10  |  2001-05-10
> 2  |  10  |  2001-05-11
> 3  |  10  |  2001-05-12
> 4  |  20  |  2001-05-11
> 5  |  20  |  2001-05-13
> 6  |  30  |  2001-05-15
>
> the "ref" field is used to match this table with an another table containing
> the description of the event in that date.
> So, in this example I have the same event (identified as '10') which is
> repeated 3 times, another event ('20') repeated twice, and an event with a
> single occurance ('30').
> now suppose that today is 2001-05-09 and i want to publish on my web-site
> the next 3 events:
>
> doing a simple SELECT i will get :
>
> 1  |  10  | 2001-05-10
> 2  |  10  |  2001-05-11
> 4  |  20  |  2001-05-11
>
> but what i really wanted was :
>
> 1  |  10  | 2001-05-10
> 4  |  20  |  2001-05-11
> 6  |  30  |  2001-05-15
>
> i.e., i don't want to get more than one occurance of the same "ref".
>
> plz help me since i'm getting crazy trying to solve this problem !!!
> keep in mind that i'll have a table with hundreds of event, so the faster
> way to get the result is the preferred :-)
>
> the web publishing system is based on zope.
>
> tnx for your help
>
> ============================
> Giorgio Andreoletti - mentelocale S.r.l.
> responsabile hardware e networking
> responsabile sviluppo web
> www.mentelocale.it
> giorgio.andreoletti@mentelocale.it
> ============================
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: problem selecting multiple records (again!)

From
Nabil Sayegh
Date:
On 05 Jun 2001 17:01:14 -0500, Manuel Sugawara wrote:
>
> something like
>
> select * from events where ref in (select distinct ref from events limit 3);
>
> will do what you want.

Not exactly, he wanted the NEXT 3 events.

[...]
> > now suppose that today is 2001-05-09 and i want to publish on my web-site
> > the next 3 events:
[...]

--
 Nabil Sayegh

Re: problem selecting multiple records (again!)

From
Manuel Sugawara
Date:
Nabil Sayegh <nsmail@sayegh.de> writes:

> On 05 Jun 2001 17:01:14 -0500, Manuel Sugawara wrote:
> >
> > something like
> >
> > select * from events where ref in (select distinct ref from events limit 3);
> >
> > will do what you want.
>
> Not exactly, he wanted the NEXT 3 events.

OK, I see; is not that simple eh?, but here is another idea, create a
view, something like:

create view events_v as select distinct on (ref) * from events where date >= now();

and then select limited to 3

select * from events_v order by date limit 3;

This may not do exactly what he wants but is closest solution i can
imagine ;-)

regards,
Manuel.

Re: problem selecting multiple records (again!)

From
"Giorgio A."
Date:
hey... i posted this mex about a week ago ...... i'm sure i didn't posted it
again !

i got the answer to my problems (tnx to all ! you're great!)

donnow what has happened..... yesterday i saw my PC for the last time at
18:30, so i can't have posted it again at 23:55 !

btw,
tnx to all again

by
Giorgio A.


----- Original Message -----
From: "Giorgio Andreoletti - mentelocale"
<giorgio.andreoletti@mentelocale.it>
To: <pgsql-novice@postgresql.org>
Sent: Wednesday, May 30, 2001 6:17 PM
Subject: [NOVICE] problem selecting multiple records (again!)


> hi everyone,
> i already wrote to this list for the same problem.... i thought i had
> resolved it, but i was wrong !
> i'll explain my problem in a different (and simplier to understand) way:
[snip..............]