Returning names that match 3 consecutive years

1

I have a table called 'participants' and I am trying to write a SQL query that returns a table containing one column "name" alphabetically where each record is a name of a participant who took part for at least three years in a row.

participants

name    year
..................
John    2003
Lyla     1994
Faith   1996
John     2002
Carol    2000
Carol    1999
John     2001
Carol    2002
Lyla       1996
Lyla       1997
Carol     2001
John      2009

The query should return only the name column alphabetically.

name  
.....
Carol
John

I think I have to use some type of aggregate function, but not sure how?

Anyone have any ideas?

Thanks!!

2

Look at the datediff function to filter by 3 years

3

cool thanks!

4

actually in your case you just have a numeric year only so this might work for you

select name
  from participants
where year(getdate()) - year > 2
order by name
5 

;WITH cte_previous_years AS (
    SELECT *,
        LAG(year, 1) OVER(PARTITION BY name ORDER BY year) AS year_minus_1, 
        LAG(year, 2) OVER(PARTITION BY name ORDER BY year) AS year_minus_2
    FROM #data 
)
SELECT DISTINCT name
FROM cte_previous_years
WHERE year_minus_1 = year - 1 AND year_minus_2 = year - 2
6

thanks!

7

thanks for this, whats the difference between your answer and the answer provider by yosiasz?

8

I see a big difference when I run the query against some data.

9

hi again, your query is giving me the following error: ERROR: function getdate() does not exist
LINE 3: where year(getdate()) - year > 2

10

your query was not working for me so i couldn't see anything... but from my quick googles, it looks like using CTE creates more columns like year, year +1, year +2 to build a sequence of years? is this recursive CTE?

11

No, that particular cte is not recursive, although ctes can be used to do recursion.

This is SQL Server forum. Apparently you are not using SQL Server. You might get better assistance on a forum designed for your particular dbms, whatever it is (MySQL, Oracle, etc.)

12

AH I'm using MySQL so no wonder it's not working

13

hi

hope this helps

A different way to do this .. cross join 3 times

create data script

drop table if exists #Temp

create table #Temp (name varchar(20), year int)
insert into #Temp select 'John ',2003
insert into #Temp select 'Lyla ', 1994
insert into #Temp select 'Faith ',1996
insert into #Temp select 'John ', 2002
insert into #Temp select 'Carol ', 2000
insert into #Temp select 'Carol ', 1999
insert into #Temp select 'John ', 2001
insert into #Temp select 'Carol ', 2002
insert into #Temp select 'Lyla ', 1996
insert into #Temp select 'Lyla ', 1997
insert into #Temp select 'Carol ', 2001
insert into #Temp select 'John ', 2009

; with cte as 
(select 
      distinct 
	      case 
		      when a.year+1 = b.year and b.year+1 = c.year then 1 else 0 
		 end as ok 
		 , a.name 
 from 
    #Temp a 
	   cross join 
   #Temp b 
      cross join 
   #Temp c 
)
select * from cte where ok =1

image
image473×605 8.57 KB