pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedinner join and limit
7+ messages / 6 participants
[nested] [flat]
* inner join and limit
@ 2010-05-26 17:34 Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
0 siblings, 6 replies; 7+ messages in thread
From: Michele Petrazzo - Unipex @ 2010-05-26 17:34 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: inner join and limit
@ 2010-05-26 19:04 Rolando Edwards <redwards@logicworks.net>
parent: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
5 siblings, 0 replies; 7+ messages in thread
From: Rolando Edwards @ 2010-05-26 19:04 UTC (permalink / raw)
To: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: inner join and limit
@ 2010-05-26 20:13 Luigi Antognini <luigi370@produs.ch>
parent: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
5 siblings, 0 replies; 7+ messages in thread
From: Luigi Antognini @ 2010-05-26 20:13 UTC (permalink / raw)
To: 'Michele Petrazzo - Unipex' <michele.petrazzo@unipex.it>; 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
FROM t2 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: inner join and limit
@ 2010-05-26 21:03 Tim Landscheidt <tim@tim-landscheidt.de>
parent: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
5 siblings, 0 replies; 7+ messages in thread
From: Tim Landscheidt @ 2010-05-26 21:03 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: inner join and limit
@ 2010-05-26 21:24 Rolando Edwards <redwards@logicworks.net>
parent: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
5 siblings, 0 replies; 7+ messages in thread
From: Rolando Edwards @ 2010-05-26 21:24 UTC (permalink / raw)
To: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>; 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@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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: inner join and limit
@ 2010-06-03 16:08 Harald Fuchs <hari.fuchs@gmail.com>
parent: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
5 siblings, 0 replies; 7+ messages in thread
From: Harald Fuchs @ 2010-06-03 16:08 UTC (permalink / raw)
To: 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.id ORDER BY t2.somedate DESC)
FROM t1
JOIN t2 ON t2.t1id = t1.id
) dummy
WHERE rank <= $N
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: inner join and limit
@ 2010-06-06 14:58 msi77 <msi77@yandex.ru>
parent: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
5 siblings, 0 replies; 7+ messages in thread
From: msi77 @ 2010-06-06 14:58 UTC (permalink / raw)
To: Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-06-06 14:58 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-26 17:34 inner join and limit Michele Petrazzo - Unipex <michele.petrazzo@unipex.it>
2010-05-26 19:04 ` Rolando Edwards <redwards@logicworks.net>
2010-05-26 20:13 ` Luigi Antognini <luigi370@produs.ch>
2010-05-26 21:03 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-05-26 21:24 ` Rolando Edwards <redwards@logicworks.net>
2010-06-03 16:08 ` Harald Fuchs <hari.fuchs@gmail.com>
2010-06-06 14:58 ` msi77 <msi77@yandex.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox