pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTo having or not to having?
2+ messages / 1 participants
[nested] [flat]
* To having or not to having?
@ 2011-10-23 21:12 Ferruccio Zamuner <nonsolosoft@diff.org>
0 siblings, 1 reply; 2+ messages in thread
From: Ferruccio Zamuner @ 2011-10-23 21:12 UTC (permalink / raw)
To: pgsql-sql
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?
Attachments:
[text/plain] example.txt (10.1K, ../../4EA4834E.7030409@diff.org/2-example.txt)
download | inline:
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: To having or not to having?
@ 2011-10-23 22:55 Ferruccio Zamuner <nonsolosoft@diff.org>
parent: Ferruccio Zamuner <nonsolosoft@diff.org>
0 siblings, 0 replies; 2+ messages in thread
From: Ferruccio Zamuner @ 2011-10-23 22:55 UTC (permalink / raw)
To: pgsql-sql
On 10/23/11 23:12, I wrote:
> Hello,
>
> I'm rusty with SQL and I've started to practice it again but I'm falling
> on this issue.
I've found first solution using WINDOWING:
SELECT d.pintime, d.a_id, d.c_id, d.value, d.id, d.sincedate,
d.todate, d.description, d.genre
FROM (SELECT pintime,a_id,c_id,value,id,sincedate,todate,description,
genre, $1::date-a.sincedate as days, min($1::date-a.sincedate) over w AS
"days 2"
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$'
WINDOW W AS (partition by c_id)
ORDER BY $1::date-a.sincedate ASC) AS d
WHERE d.days=d."days 2"
ORDER BY d.c_id;
I'm sure that there are many other solutions and probably mine could be
not the best. So I'm looking for more hint and suggestions.
Thank you again. \ferz
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-10-23 22:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-23 21:12 To having or not to having? Ferruccio Zamuner <nonsolosoft@diff.org>
2011-10-23 22:55 ` Ferruccio Zamuner <nonsolosoft@diff.org>
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