Обсуждение: 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
I can only see a LIMIT 1 possible. If someone can come up with LIMIT N on this one, please let us all know. rolando=# drop table if exists t2; DROP TABLE rolando=# drop table if exists t1; DROP TABLE rolando=# create table t1 ( id int primary key, title varchar(10) ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE rolando=# insert into t1 values (1,'abcde'),(2,'fghi'); INSERT 0 2 rolando=# create table t2 (id int primary key,t1id int not null references t1 (id) ,somedate date); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2" CREATE TABLE rolando=# insert into t2 values rolando-# (1,1,'2010-05-23'), rolando-# (2,1,'2010-05-24'), rolando-# (3,1,'2010-05-25'), rolando-# (4,2,'2010-05-22'), rolando-# (5,2,'2010-05-26'); INSERT 0 5 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 t1.id,t2.id,t2.somedate rolando-# from t1,t2,(select t1id,max(somedate) as somedate from t2 group by t1id) t3 rolando-# where t1.id=t2.t1id rolando-# and t2.somedate=t3.somedate;id | id | somedate ----+----+------------ 1 | 3 | 2010-05-25 2 | 5 | 2010-05-26 (2 rows) Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx redwards@logicworks.net http://www.linkedin.com/in/rolandoedwards -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Michele Petrazzo - Unipex Sent: Wednesday, May 26, 2010 1:35 PM To: pgsql-sql@postgresql.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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Hello Here a suggestion for your problem. SELECT a.id AS t1_id, d.id AS t2_id, d.somedate AS t2_somedate FROM t1 a JOIN ( SELECT id, t1id, somedate FROM t2 b WHERE (t1id, somedate) IN ( SELECT t1id, somedate FROMt2 c WHERE c.t1id = b.t1id ORDER BY somedate DESC LIMIT 1 ) ) d ON (a.id=d.t1id); t1_id | t2_id | t2_somedate -------+-------+------------- 1 | 3 | 2010-05-25 2 | 5 | 2010-05-26 (2 rows) Hope this helps Regards (Saluti da Zurigo) Luigi Antognini -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Michele Petrazzo - Unipex Sent: Wednesday, May 26, 2010 7:35 PM To: pgsql-sql@postgresql.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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Michele Petrazzo - Unipex <michele.petrazzo@unipex.it> wrote: > 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? In addition to Luigi's and Rolando's responses, there are of course the always glorious "DISTINCT ON" for the "LIMIT 1" case: | SELECT DISTINCT ON (t1.id) t1.id, t2.id, t2.somedate FROM t1 JOIN t2 ON t1.id = t2.t1id ORDER BY t1.id, somedate DESC; and window functions for the generic one: | SELECT t1_id, t2_id, t2_somedate FROM | (SELECT t1.id AS t1_id, | t2.id AS t2_id, | t2.somedate AS t2_somedate, | ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.somedate DESC) AS rn | FROM t1 JOIN t2 ON t1.id = t2.t1id) AS SubQuery | WHERE rn <= 2; Tim
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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Michele Petrazzo - Unipex Sent: Wednesday, May 26, 2010 1:35 PM To: pgsql-sql@postgresql.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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
In article <4BFD5BC0.90900@unipex.it>, Michele Petrazzo - Unipex <michele.petrazzo@unipex.it> writes: > 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). If you also want LIMIT N, the easiest way is probably the use of a window function (PostgreSQL >= 8.4): SELECT i1, i2, somedate FROM ( SELECT t1.id AS i1, t2.id AS i2, t2.somedate, rank() OVER (PARTITION BY t1.idORDER BY t2.somedate DESC) FROM t1 JOIN t2 ON t2.t1id = t1.id ) dummy WHERE rank <= $N
Hi, Some ways to do that: http://www.sql-ex.ru/help/select16.php > 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 > Здесь спама нет http://mail.yandex.ru/nospam/sign