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
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
