r_slot (4/28/2015)

---

Hello experts,

I want to show a resultset from the table mentioned below that only shows the longest range. In this example we talk about archery, an olympic event that has been won by the Korean ladies since the introduction in 1988. This means that the socalled streak is 7 (1988, 1992, 1996, 2000, 2004, 2008 and 2012).

The result should show the streak, sport, event, gender, team/country, first year of victory and last year of victory. When there is one year with no victory the streak should be set to 0 and the counting starts all over again.

The column 'number' says that if the year before that country won it is 1, otherwise it is NULL. A solution with and without the column 'Number' would be appreciated.

The data:

IF OBJECT_ID('TempDB..#mySeries','U') IS NOT NULL

DROP TABLE #mySeries

CREATE TABLE #mySeries

(

ID INT PRIMARY KEY CLUSTERED,

Number nvarchar(5),

Sport nvarchar(max),

Event nvarchar(max),

Gender nvarchar(10),

Country nvarchar(max),

Year_From INT,

Year_To INT

)

INSERT INTO #mySeries (ID, Number, Sport, Event, Gender, Country, Year_From, Year_To)

SELECT '1', '1', 'Archery', 'Individual', 'Male','United States', '1972','1972' UNION ALL

SELECT '2', '1', 'Archery','Individual','Male', 'United States', '1976','1976' UNION ALL

SELECT '3', '1', 'Archery','Individual','Male', 'United States', '1988', '1988' UNION ALL

SELECT '4', '1', 'Archery','Individual','Female', 'South Korea', '1988', '1988' UNION ALL

SELECT '5', '1', 'Archery','Individual','Female', 'South Korea', '1992','1992' UNION ALL

SELECT '6', '1', 'Archery','Individual','Female', 'South Korea', '1996','1996' UNION ALL

SELECT '7', '1', 'Archery','Individual','Female', 'South Korea', '2000','2000' UNION ALL

SELECT '8', '1', 'Archery','Individual','Female', 'South Korea', '2004','2004' UNION ALL

SELECT '9', '1', 'Archery','Individual','Female', 'United States', '1972','1972' UNION ALL

SELECT '10', '1', 'Archery', 'Individual','Female', 'United States', '1976','1976' UNION ALL

SELECT '11', 'NULL', 'Archery','Individual','Female', 'Soviet Union','1980','1980' UNION ALL

SELECT '12', 'NULL', 'Archery','Individual','Female', 'South Korea', '1984','1984' UNION ALL

SELECT '13', 'NULL', 'Archery','Individual','Female', 'China', '2008','2008' UNION ALL

SELECT '14', 'NULL', 'Archery','Individual','Female', 'South Korea', '2012','2012' UNION ALL

SELECT '15', 'NULL', 'Archery','Individual','Male', 'France','1992','1992' UNION ALL

SELECT '16', 'NULL', 'Archery','Individual','Male', 'United States', '1996','1996' UNION ALL

SELECT '17', 'NULL', 'Archery','Individual','Male', 'Australia', '2000','2000' UNION ALL

SELECT '18','NULL', 'Archery','Individual','Male', 'Italy','2004','2004' UNION ALL

SELECT '19', 'NULL', 'Archery','Individual','Male', 'Ukraine','2008','2008' UNION ALL

SELECT '20', 'NULL', 'Archery','Individual','Male', 'South Korea', '2012','2012' UNION ALL

SELECT '21', 'NULL', 'Archery','Individual','Male', 'Finland','1980','1980' UNION ALL

SELECT '22', 'NULL', 'Archery','Individual','Male', 'United States', '1984','1984'UNION ALL

SELECT '23', '1','Archery','Teams', 'Male', 'South Korea', '1988', '1988' UNION ALL

SELECT '24', '1','Archery','Teams', 'Female', 'South Korea', '1988', '1988' UNION ALL

SELECT '25', '1','Archery','Teams', 'Female', 'South Korea', '1992','1992' UNION ALL

SELECT '26', '1','Archery','Teams', 'Female', 'South Korea', '1996','1996' UNION ALL

SELECT '27', '1','Archery','Teams', 'Female', 'South Korea', '2000','2000' UNION ALL

SELECT '28', '1','Archery','Teams', 'Female', 'South Korea', '2004','2004' UNION ALL

SELECT '29', '1','Archery', 'Teams', 'Female', 'South Korea', '2008','2008' UNION ALL

SELECT '30', '1','Archery', 'Teams', 'Female', 'South Korea', '2012','2012' UNION ALL

SELECT '31', '1','Archery', 'Teams', 'Male', 'South Korea', '2004','2004' UNION ALL

SELECT '32', '1','Archery', 'Teams', 'Male', 'South Korea', '2008','2008' UNION ALL

SELECT '33', 'NULL','Archery', 'Teams', 'Male', 'Italy','2012','2012' UNION ALL

SELECT '34', 'NULL','Archery', 'Teams', 'Male', 'Spain','1992','1992' UNION ALL

SELECT '35', 'NULL','Archery', 'Teams', 'Male', 'United States', '1996','1996' UNION ALL

SELECT '36', 'NULL','Archery', 'Teams', 'Male', 'South Korea', '2000','2000'

GO

Any help is very much appreciated.

Kind regards,

Robert