Thread: To having or not to having?

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

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