From: | Rolando Edwards <redwards(at)logicworks(dot)net> |
---|---|
To: | Michele Petrazzo - Unipex <michele(dot)petrazzo(at)unipex(dot)it>, "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: inner join and limit |
Date: | 2010-05-26 21:24:27 |
Message-ID: | 0ED65673FA8E634AAB252741A6CD3B5E04B7056229@lw-exchange4b.us.logicworks.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I found a good solution.
drop table if exists t3;
drop table if exists t2;
drop table if exists t1;
create table t1 ( id int primary key, title varchar(10) );
insert into t1 values (1,'abcde'),(2,'fghi');
create table t2 (id int primary key,t1id int not null references t1 (id) ,somedate date);
insert into t2 values
(1,1,'2010-05-23'),
(2,1,'2010-05-24'),
(3,1,'2010-05-25'),
(4,2,'2010-05-22'),
(5,2,'2010-05-26');
create table t3 ( t1id int not null,idcount int,vector varchar[][] );
insert into t3 (t1id,idcount) select t1id,count(1) as idcount from t2 group by t1id;
update t3 set vector=array(select somedate from t2 where t2.t1id=t3.t1id ORDER BY somedate DESC LIMIT 2);
select * from t1;
select * from t2;
select * from t3;
I got this output...
rolando=# select * from t1;
id | title
----+-------
1 | abcde
2 | fghi
(2 rows)
rolando=# select * from t2;
id | t1id | somedate
----+------+------------
1 | 1 | 2010-05-23
2 | 1 | 2010-05-24
3 | 1 | 2010-05-25
4 | 2 | 2010-05-22
5 | 2 | 2010-05-26
(5 rows)
rolando=# select * from t3;
t1id | idcount | vector
------+---------+------------------------
2 | 2 | {2010-05-26,2010-05-22}
1 | 3 | {2010-05-25,2010-05-24}
Notice the arrays are length 2 because I have the clause "LIMIT 2" in the UPDATE command.
You can set LIMIT to whatever. Give it a try !!!
BTW I think another person already replied a solution without an extra table. Give that a try, too !!!
-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Michele Petrazzo - Unipex
Sent: Wednesday, May 26, 2010 1:35 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] inner join and limit
Hi list,
I have two table that are so represented:
t1:
id int primary key
... other
t2:
id int primary key
t1id int fk(t1.id)
somedate date
... other
data t1:
1 | abcde
2 | fghi
data t2:
1 | 1 | 2010-05-23
2 | 1 | 2010-05-24
3 | 1 | 2010-05-25
4 | 2 | 2010-05-22
5 | 2 | 2010-05-26
I'm trying to create a query where the data replied are:
join t1 with t2 and return only the LIMIT 1 (or N) of the t2, with date
order (of t2).
Data should be:
t1.id | t2.id | t2,somedate
1 | 3 | 2010-05-25
2 | 5 | 2010-05-26
As said, I'm trying, but without success...
Can be done for you?
Thanks,
Michele
From | Date | Subject | |
---|---|---|---|
Next Message | Gerardo Herzig | 2010-05-26 22:02:20 | cast record type to array? |
Previous Message | Tim Landscheidt | 2010-05-26 21:03:49 | Re: inner join and limit |