CODEWITHSUNDEEP
sql
RickMaia
RickMaia

Reputation: 13

Querying an SQL table to get all new values in a column according to filter in another column

Sorry I couldn't make the question clearer, let me explain it here.

I have a table that has two columns:

year, ID

------------

2013, 01

2013, 27

2013, 33

2014, 22

2014, 33

2014, 01

2014, 13

2015, 45

2015, 13

2015, 22

What I want to do is the following: check all new IDs that appeared in 2014 from 2013, and all new IDs that appeared in 2015 from 2014, and so on and so forth...

So, from the example above, the expected answer to my query should be a table like:

year, new
-------------

2014, 2

2015, 1

Since in 2014 there are two new IDs in relation to 2013 (22 and 13), and in 2015 there is one new ID in relation to 2014 (45).

Sorry for the horrible formatting, I'm new to this site. Any help is appreciated, thanks.

Upvotes: 1

Views: 49

Answers (2)

zip
zip

Reputation: 4061

You need to find the all codes that didn't exist with a year y - 1 that shower at year y:

 with cte as
 (
select cast(2013 as int) as year, '01' as id

union select 2013, 27

union select 2013, 33

union select 2014, 22

union select 2014, 33

union select 2014, '01'

union select 2014, 13

union select 2015, 45

union select 2015, 13

union select 2015, 22
)
select year, count(*) from cte a where not exists(select 1 from cte b where a.year -1 = b.year and a.id = b.id)
and a.year -1 in (select distinct year from cte)
group by year

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270573

If I understand correctly, you want lag():

select year, count(*)
from (select t.*, lag(year) over (partition by id order by year) as prev_year
      from t
     ) t
where prev_year is null or prev_year < year - 1
group by year;

Upvotes: 1

Related Questions