Re: [SQL] inner join and limit

2010-05-26 Thread Rolando Edwards 
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
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] inner join and limit

2010-05-26 Thread Rolando Edwards 
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-ow...@postgresql.org [mailto:pgsql-sql-ow...@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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for c1

2011-02-08 Thread Rolando Edwards 
SELECT distinct c1,array_to_string(array(SELECT c2 FROM T1 B where 
A.c1=B.c1),', ') from T1 A order by c1;

Give it a Try !!!

Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Emi Lu
Sent: Tuesday, February 08, 2011 2:36 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] "select c1, method(c2) group by c1" returns all values of c2 for 
c1

Good afternoon,

Is there a method to retrieve the following results:

T1(c1 int, c2 varchar(128) )
-


(1, val1);
(1, val2);
(1, val3);
(2, val1);
(3, val5);
(3, val6);

select c1, method(c2)
group by c1

returns:

1,   "val1, val2, val3"
2,   "val1"
3,   "val5, val6"


Thanks a lot!

--
Lu Ying

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql