select uniques names when a column is not sooner than a specific date-CodePudding

I'm trying to do a raw query here, which I don't know if it's possible or not.

This is the table schema:

Table name: army_attack

Columns:

army_name varchar
created_at date
attacked_to varchar
first_attacked_at date

I want to retrieve all the armies names whose first attack was at year 1885.

I can simply do:

select distinct(army_name) 
from army_attack 
where first_attacked_at between '1885-1-1' and '1885-12-31'

but what if that an army also had attacks before 1885? Is there any window function to do that? or anything that might help?

CodePudding user response：

use a subquery to extract armies which first attack are before the year

SELECT DISTINCT(army_name) from army_attack
WHERE first_attacked_at BETWEEN '1885-1-1' and '1885-12-31' 
and army_name not in 
(select army_name from army_attack where first_attacked_at <='1884')

CodePudding user response：

SELECT * FROM army_attack
where first_attacked_at between '1885-1-1' and '1885-12-31' AND
attacked_to IN (SELECT attacked_to FROM army_attack where created_at < '1885-1-1')