Postgres Pro
Downloads
Home   >   mailing lists

Thread: problems selecting multiple records (again!)

problems selecting multiple records (again!)

From
"Giorgio A."
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 A.

Re: problems selecting multiple records (again!)

From
Nabil Sayegh
Date:
On 30 May 2001 21:57:45 +0200, Giorgio A. wrote:
> but what i really wanted was :
>
> 1  |  10  | 2001-05-10
> 4  |  20  |  2001-05-11
> 6  |  30  |  2001-05-15

CREATE TEMP TABLE tmp (ref int, dat date);
INSERT INTO tmp values (10, '2001-05-10');
INSERT INTO tmp values (10, '2001-05-11');
INSERT INTO tmp values (10, '2001-05-12');
INSERT INTO tmp values (20, '2001-05-11');
INSERT INTO tmp values (20, '2001-05-13');
INSERT INTO tmp values (30, '2001-05-15');
SELECT ref, min(dat) from tmp group by ref order by min(dat) limit 3;

 ref |    min
-----+------------
  10 | 10.05.2001
  20 | 11.05.2001
  30 | 15.05.2001
(3 rows)

--
 Nabil Sayegh

Re: problems selecting multiple records (again!)

From
"Giorgio A."
Date:
tnx everyone !
now i'm sure i solved my problem !

i knew there was a simple way to do it, but i really wasn't able to find it
!

tnx again,
i'll be your slave for the next century :-)

Giorgio A.
www.mentelocale.it