Thread: inner join and limit

inner join and limit

From

Michele Petrazzo - Unipex

Date:

26 May 2010, 17:44:06

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

Re: inner join and limit

From

Rolando Edwards

Date:

26 May 2010, 19:09:30

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

Re: inner join and limit

From

"Luigi Antognini"

Date:

26 May 2010, 20:41:48

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

Re: inner join and limit

From

Tim Landscheidt

Date:

26 May 2010, 21:04:20

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

Re: inner join and limit

From

Rolando Edwards

Date:

26 May 2010, 21:24:41

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

Re: inner join and limit

From

Harald Fuchs

Date:

03 June 2010, 16:09:05

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

Re: inner join and limit

From

msi77

Date:

06 June 2010, 15:06:20

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