CODEWITHSUNDEEP
databasepostgresql
JM4
JM4

Reputation: 6788

How to pull results based on a minimum date in PostgreSQL

I'm still not too familiar with PostgreSQL but understand you can't have aggregate functions in WHERE clauses.

I have data that looks like this:

dID |  status  | created_at
---------------------------
2   |    2     |  2014-01-01
2   |    2     |  2014-02-01
3   |    2     |  2013-12-22

I am essentially trying to build a query that will return all results from a data set based on a minimum created_at date. The following query won't work but it does highlight what I'm trying to accomplish:

select
  * 
from table1 a
JOIN table2 b ON (a.id = b.id)
where a.did =  2
  AND b.status = 2
  AND MIN(b.created_at) < '2014-01-31'

The sample table above is aggregated from a very large dataset but I basically want to find a list of dID's that have a certain status where the created_at date was defined at a certain time period. Any suggestions?

Upvotes: 0

Views: 113

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324375

I think you just want the HAVING clause, though it's hard to be sure from your question:

select did
from t
group by did
having min(created_at) < '2014-01-31';

If you want to get data from another table by did you can use an EXISTS subquery, albeit potentially with performance issues:

SELECT *
FROM t1 
WHERE EXISTS (
   SELECT 1
   FROM t2
   WHERE t1.did = t2.did
   GROUP BY t2.did
   HAVING min(t2.created_at) < '2014-01-31'
);

... or you can join on a query that returns the set of eligible DIDs.

Upvotes: 1

Pavel Stehule
Pavel Stehule

Reputation: 45770

maybe:

postgres=# SELECT DISTINCT ON (id, status) * 
              FROM table1 
             WHERE id = 2 and status = 2 and created_at < '2014-01-31' 
             ORDER BY id, status, created_at; 
 id | status | created_at 
----+--------+------------
  2 |      2 | 2014-01-01
(1 row)

Upvotes: 1

Related Questions