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?