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 | 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?
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
