Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id A168EB5DCA5 for ; Sun, 23 Oct 2011 18:22:28 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 72921-09 for ; Sun, 23 Oct 2011 21:22:22 +0000 (UTC) X-Greylist: delayed 00:07:53.120964 by SQLgrey-1.8.0-rc2 Received: from u1.diff.org (u1.diff.org [78.46.96.43]) by mail.postgresql.org (Postfix) with ESMTP id 5CEC3B5DBD8 for ; Sun, 23 Oct 2011 18:22:21 -0300 (ADT) Received: from lap.diff.org (vola.diff.org [81.174.26.135]) (authenticated bits=0) by u1.diff.org (8.14.4/8.14.4) with ESMTP id p9NLENV9028692 for ; Sun, 23 Oct 2011 21:14:26 GMT (envelope-from nonsolosoft@diff.org) Message-ID: <4EA4834E.7030409@diff.org> Date: Sun, 23 Oct 2011 23:12:46 +0200 From: Ferruccio Zamuner Organization: NonSoLoSoft User-Agent: Mozilla/5.0 (X11; U; DragonFly i386; en-US; rv:1.9.2.9) Gecko/20101023 Lightning/1.0b3pre Lanikai/3.1.3 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: To having or not to having? Content-Type: multipart/mixed; boundary="------------060302070301030008010002" X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.3.5 (u1.diff.org [78.46.96.43]); Sun, 23 Oct 2011 21:14:26 +0000 (UTC) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.505 tagged_above=-5 required=5 tests=BAYES_40=-0.001, RP_MATCHES_RCVD=-0.504 X-Spam-Level: X-Archive-Number: 201110/58 X-Sequence-Number: 35942 This is a multi-part message in MIME format. --------------060302070301030008010002 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hello, I'm rusty with SQL and I've started to practice it again but I'm falling on this issue. The problem: Extracting rows from 'b' table trapping min() of a calculated value "days" on 'a' table and a parameter. SELECT b.*, $1::date-a.sincedate AS "days" FROM b, a WHERE pintime BETWEEN $2 AND $2::interval+'00:01:00'::interval AND b.a_id=a.id AND a.genre='F' AND description ~*'35$' ORDER BY $1::date-a.sincedate ASC; attached there is the full example, data, creates and inserts for it. $1 can by any date (now() for example is good enough) $2 is a time interval (10:00 in the example). I thank you in advance for any answer. Bye, \ferz --------------060302070301030008010002 Content-Type: text/plain; name="example.txt" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="example.txt" The problem: SELECT pintime,a_id,c_id,value,id,sincedate,todate,description, genre, now()::date-a.sincedate AS "days" FROM b, a WHERE pintime BETWEEN '10:00:00' AND '10:00:00'::interval+'00:01:00'::interval AND b.a_id=a.id AND a.genre='F' AND description ~*'35$' ORDER BY now()::date-a.sincedate ASC; pintime | a_id | c_id | value | id | sincedate | todate | description | genre | days ----------+------+------+-------+----+------------+------------+-------------+-------+------ 10:00:00 | 2 | 1 | 1100 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 2 | 1200 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 3 | 1300 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 4 | 1400 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 5 | 1500 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 4 | 1 | 4100 | 4 | 2011-03-11 | 2011-03-23 | a 35 | F | 226 10:00:00 | 4 | 2 | 4200 | 4 | 2011-03-11 | 2011-03-23 | a 35 | F | 226 10:00:00 | 4 | 3 | 4300 | 4 | 2011-03-11 | 2011-03-23 | a 35 | F | 226 10:00:00 | 4 | 4 | 4400 | 4 | 2011-03-11 | 2011-03-23 | a 35 | F | 226 10:00:00 | 4 | 5 | 4500 | 4 | 2011-03-11 | 2011-03-23 | a 35 | F | 226 10:00:00 | 5 | 1 | 5100 | 5 | 2010-09-02 | 2010-10-10 | a 35 | F | 416 10:00:00 | 5 | 4 | 5400 | 5 | 2010-09-02 | 2010-10-10 | a 35 | F | 416 10:00:00 | 5 | 6 | 10600 | 5 | 2010-09-02 | 2010-10-10 | a 35 | F | 416 (13 rows) I need to extract rows having the lower number of days for each a_id,c_id pair. The wanted result of the new query has to be: pintime | a_id | c_id | value | id | sincedate | todate | description | genre | days ----------+------+------+-------+----+------------+------------+-------------+-------+------ 10:00:00 | 2 | 1 | 1100 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 2 | 1200 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 3 | 1300 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 4 | 1400 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 5 | 1500 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 6 | 6 | 10600 | 6 | 2010-09-02 | 2010-10-10 | a 35 | F | 416 (13 rows) Since c_id=6 has not any result for days<416; So which are ways to write such SQL query? Data: create table a (id integer primary key, sincedate date not null, todate date not null, description text, genre char(1) default 'M'); create table c (id integer primary key, name text not null, someotherdata text); create table b (pintime interval not null, a_id integer not null references a, c_id integer not null references c, value integer, primary key(a_id,c_id)); insert into a (id,sincedate, todate, description, genre) values (1,'20110502','20110527','a 15','F'), (2,'20110502','20110527','a 35','F'), (3,'20110502','20110527','b 35','M'), (4,'20110311','20110323','a 35','F'), (5,'20100902','20101010','a 35','F'); insert into c (id,name) values (1,'c1'),(2,'c2'),(3,'c3'),(4,'c4'),(5,'c5'),(6,'c6'); insert into b (pintime,a_id,c_id,value) values ('10:00',1,1,100),('10:00',1,2,200),('10:00',1,3,300),('10:00',1,4,400),('10:00',1,5,500); insert into b (pintime,a_id,c_id,value) values ('10:00',2,1,1100),('10:00',2,2,1200),('10:00',2,3,1300),('10:00',2,4,1400),('10:00',2,5,1500); insert into b (pintime,a_id,c_id,value) values ('10:00',3,1,3100),('10:00',3,2,3200),('10:00',3,3,3300),('10:00',3,4,3400),('10:00',3,5,3500); insert into b (pintime,a_id,c_id,value) values ('10:00',4,1,4100),('10:00',4,2,4200),('10:00',4,3,4300),('10:00',4,4,4400),('10:00',4,5,4500); insert into b (pintime,a_id,c_id,value) values ('10:00',5,1,5100),('10:00',5,4,5400),('10:00',5,6,10600); select * from a; id | sincedate | todate | description | genre ----+------------+------------+-------------+------- 1 | 2011-05-02 | 2011-05-27 | a 15 | F 2 | 2011-05-02 | 2011-05-27 | a 35 | F 3 | 2011-05-02 | 2011-05-27 | b 35 | M 4 | 2011-03-11 | 2011-03-23 | a 35 | F 5 | 2010-09-02 | 2010-10-10 | a 35 | F (6 rows) select * from b; pintime | a_id | c_id | value ----------+------+------+------- 10:00:00 | 1 | 1 | 100 10:00:00 | 1 | 2 | 200 10:00:00 | 1 | 3 | 300 10:00:00 | 1 | 4 | 400 10:00:00 | 1 | 5 | 500 10:00:00 | 2 | 1 | 1100 10:00:00 | 2 | 2 | 1200 10:00:00 | 2 | 3 | 1300 10:00:00 | 2 | 4 | 1400 10:00:00 | 2 | 5 | 1500 10:00:00 | 3 | 1 | 3100 10:00:00 | 3 | 2 | 3200 10:00:00 | 3 | 3 | 3300 10:00:00 | 3 | 4 | 3400 10:00:00 | 3 | 5 | 3500 10:00:00 | 4 | 1 | 4100 10:00:00 | 4 | 2 | 4200 10:00:00 | 4 | 3 | 4300 10:00:00 | 4 | 4 | 4400 10:00:00 | 4 | 5 | 4500 10:00:00 | 5 | 1 | 5100 10:00:00 | 5 | 4 | 5400 10:00:00 | 5 | 6 | 10600 (23 rows) select * from c; id | name | someotherdata ----+------+--------------- 1 | c1 | 2 | c2 | 3 | c3 | 4 | c4 | 5 | c5 | 6 | c6 | (6 rows) SELECT *, now()::date-a.sincedate AS "days" FROM b, a WHERE pintime BETWEEN '10:00:00' AND '10:00:00'::interval+'00:01:00'::interval AND c_id=5 AND b.a_id=a.id AND a.genre='F' AND description ~*'35$'; pintime | a_id | c_id | value | id | sincedate | todate | description | genre | days ----------+------+------+-------+----+------------+------------+-------------+-------+------ 10:00:00 | 2 | 5 | 1500 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 4 | 5 | 4000 | 4 | 2011-03-11 | 2011-03-23 | a 35 | F | 226 (2 rows) How to write a query to choose the result a.id with with less days? pintime | a_id | c_id | value | id | sincedate | todate | description | genre | days ----------+------+------+-------+----+------------+------------+-------------+-------+------ 10:00:00 | 2 | 5 | 1500 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 This is the wrong way: SELECT pintime,a_id,c_id,value,id,sincedate,todate,description, genre, now()::date-a.sincedate AS "days" FROM b, a WHERE pintime BETWEEN '10:00:00' AND '10:00:00'::interval+'00:01:00'::interval AND c_id=5 AND b.a_id=a.id AND a.genre='F' AND description ~*'35$' GROUP BY pintime,a_id,c_id,value,id,sincedate,todate,description, genre HAVING now()::date-a.sincedate=min(now()::date-a.sincedate); pintime | a_id | c_id | value | id | sincedate | todate | description | genre | days ----------+------+------+-------+----+------------+------------+-------------+-------+------ 10:00:00 | 4 | 5 | 4000 | 4 | 2011-03-11 | 2011-03-23 | a 35 | F | 226 10:00:00 | 2 | 5 | 1500 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 (2 rows) This gives the right result: SELECT pintime,a_id,c_id,value,id,sincedate,todate,description, genre, now()::date-a.sincedate AS "days" FROM b, a WHERE pintime BETWEEN '10:00:00' AND '10:00:00'::interval+'00:01:00'::interval AND c_id=5 AND b.a_id=a.id AND a.genre='F' AND description ~*'35$' ORDER BY now()::date-a.sincedate ASC LIMIT 1; but what if I use this without the c_id=5 condition? Then the problem (again): SELECT pintime,a_id,c_id,value,id,sincedate,todate,description, genre, now()::date-a.sincedate AS "days" FROM b, a WHERE pintime BETWEEN '10:00:00' AND '10:00:00'::interval+'00:01:00'::interval AND b.a_id=a.id AND a.genre='F' AND description ~*'35$' ORDER BY now()::date-a.sincedate ASC; pintime | a_id | c_id | value | id | sincedate | todate | description | genre | days ----------+------+------+-------+----+------------+------------+-------------+-------+------ 10:00:00 | 2 | 1 | 1100 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 2 | 1200 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 3 | 1300 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 4 | 1400 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 5 | 1500 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 4 | 1 | 4100 | 4 | 2011-03-11 | 2011-03-23 | a 35 | F | 226 10:00:00 | 4 | 2 | 4200 | 4 | 2011-03-11 | 2011-03-23 | a 35 | F | 226 10:00:00 | 4 | 3 | 4300 | 4 | 2011-03-11 | 2011-03-23 | a 35 | F | 226 10:00:00 | 4 | 4 | 4400 | 4 | 2011-03-11 | 2011-03-23 | a 35 | F | 226 10:00:00 | 4 | 5 | 4500 | 4 | 2011-03-11 | 2011-03-23 | a 35 | F | 226 10:00:00 | 5 | 1 | 5100 | 5 | 2010-09-02 | 2010-10-10 | a 35 | F | 416 10:00:00 | 5 | 4 | 5400 | 5 | 2010-09-02 | 2010-10-10 | a 35 | F | 416 10:00:00 | 5 | 6 | 10600 | 5 | 2010-09-02 | 2010-10-10 | a 35 | F | 416 (13 rows) I need to extract rows having the lower number of days for each a_id,c_id pair. The wanted result of the new query has to be: pintime | a_id | c_id | value | id | sincedate | todate | description | genre | days ----------+------+------+-------+----+------------+------------+-------------+-------+------ 10:00:00 | 2 | 1 | 1100 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 2 | 1200 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 3 | 1300 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 4 | 1400 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 2 | 5 | 1500 | 2 | 2011-05-02 | 2011-05-27 | a 35 | F | 174 10:00:00 | 6 | 6 | 10600 | 6 | 2010-09-02 | 2010-10-10 | a 35 | F | 416 (13 rows) Since c_id=6 has not any result for days<416; So which are ways to write such SQL query? --------------060302070301030008010002--