CODEWITHSUNDEEP
sqlpostgresql
bob
bob

Reputation: 61

Get 7 days oldest record

I have query for get data 7 days oldest but failed with 'between'. my query:

SELECT b.sequence_number, b.message
FROM test.tab1 a, test.tab2 b
WHERE a.id = b.id 
AND a.delivery_date between (select min(delivery_date) from test.tab1) 
and (select min(delivery_date)-7 from test.tab1)

but this query can't get result. how to fix this issue?

Upvotes: 0

Views: 42

Answers (2)

Fahmi
Fahmi

Reputation: 37473

You need to fix the between order, smaller one should be first then larger one

SELECT b.sequence_number, b.message
FROM test.tab1 a, test.tab2 b
WHERE a.id = b.id 
AND a.delivery_date between (select min(delivery_date)-7 from test.tab1)
and (select min(delivery_date) from test.tab1)

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

if you want last 7 days records from current date then query will be

    SELECT b.sequence_number, b.message
    FROM test.tab1 a, test.tab2 b
    WHERE a.id = b.id 
    AND a.delivery_date>=current_date - interval '7 days'

but if want 7days from you min date then it would be 

SELECT b.sequence_number, b.message
        FROM test.tab1 a, test.tab2 b
        WHERE a.id = b.id 
        AND a.delivery_date>=(select min(delivery_date)-7 from test.tab1) and a.delivery_date <=(select min(delivery_date) from test.tab1)

Upvotes: 0

Related Questions