CODEWITHSUNDEEP
sqlpostgresql
JohnDoe
JohnDoe

Reputation: 2513

Repeat a SQL query

Assume this very simple SQL query:

SELECT * FROM a WHERE time < '2010-01-01'

Now, how can I assemble a query where the time part is actually an 'array'?

Somehow between the lines of:

SELECT * FROM a WHERE time < ['2010-01-01', '2012-01-01']

The Select should be executed two times but result in a single result set.

Note, that this sample array contains only two items, but it may contain many more, as the results are actually coming from a sub-query.

Postgresql 9.3

Upvotes: 0

Views: 747

Answers (4)

milan minarovic
milan minarovic

Reputation: 123

If you say that Time is coming from subquery, you can include that subquery into Non Equi join (<):

SELECT DISTINCT A.*
FROM Table_1 AS A 
INNER JOIN (SELECT DISTINCT Time FROM DATE_Array_Table) AS B
       on (A.Time < B.Time)

There is INNER JOIN on table with array of dates, in this case of course you will get duplicates, but querying only table A with Distinct values will solve that easily.

In this case it does not matter how many Time value you will have (1,2,3,4, ...). You have wrote that your final query is with aggregation, so be aware of duplication in my solution.

Upvotes: 0

Walker Farrow
Walker Farrow

Reputation: 3875

This should do the trick - use ANY as in:

select *
from a
where time < any(array['2010-01-01'::timestamp, '2012-01-01'::timestamp])

Upvotes: 0

gordy
gordy

Reputation: 9786

ANY works for this, e.g:

SELECT * FROM a WHERE time < ANY (SELECT '2010-01-01' UNION ALL SELECT '2012-01-01')

Upvotes: 2

G B
G B

Reputation: 1462

Perhaps you have given a bad sample SQL because SELECT * FROM a WHERE time < '2012-01-01' will give you all the dates including '2010-01-01'

If you are just wanting to combine two identically structured reultsets into one then UNION operator will help you here:

SELECT * FROM a WHERE time < '2010-01-01'
UNION
SELECT * FROM a WHERE time < '2012-01-01'

If you wanted to include the results of < '2010-01-01' from both queries you would need to use the UNION ALL

Upvotes: 0

Related Questions