InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 17:41:48 GMT20http://www.sqlservercentral.com/Forums/Topic1233619-392-1.aspx[quote][b]drew.allen (1/11/2012)[/b][hr]That's true, but this statement from the original poster sounds like the GROUP BY is the correct version.[quote][b]kevin_nikolai (1/10/2012)[/b][hr]If [b]NOT[/b] 1st year and same Programme for same Student, then return no results.[/quote]He say [b]1st[/b] year, not 1st [b]consecutive[/b] year. Of course, that could simply be a result of poor wording on the part of the OP.[/quote]My point was just that a student might have more than one first year in the same subject (or programme), and the two approaches would produce different results in that case. The GROUP BY/HAVING does also have the drawback of requiring a scan, whereas the NOT EXISTS one could use a seek, of course.Wed, 11 Jan 2012 16:34:07 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1233619-392-1.aspx[quote][b]drew.allen (1/11/2012)[/b][hr]I think it's actually even simpler.[/quote][quote][b]SQL Kiwi (1/11/2012)[/b][hr]That has a different semantic though. Example:[/quote]That's true, but this statement from the original poster sounds like the GROUP BY is the correct version.[quote][b]kevin_nikolai (1/10/2012)[/b][hr]If [b]NOT[/b] 1st year and same Programme for same Student, then return no results.[/quote]He say [b]1st[/b] year, not 1st [b]consecutive[/b] year. Of course, that could simply be a result of poor wording on the part of the OP.DrewWed, 11 Jan 2012 15:04:34 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1233619-392-1.aspx[quote][b]drew.allen (1/11/2012)[/b][hr]I think it's actually even simpler.[/quote]That has a different semantic though. Example:[code="sql"]CREATE TABLE #Students ( StudentID integer, Programme varchar(20), [Year] integer);INSERT #Students (StudentID, Programme, [Year])VALUES (1, 'P1', 2000), (1, 'P2', 2001), (1, 'P1', 2002), (1, 'P3', 2003);[/code][code="sql"]SELECT * FROM #Students AS sWHERE s.[Year] = 2002 AND NOT EXISTS ( SELECT * FROM #Students AS s2 WHERE s2.StudentID = s2.StudentID AND s2.Programme = s.Programme AND s2.[Year] = s.[Year] - 1 ); SELECT StudentID, Programme, Min([Year]) AS [Year]FROM #StudentsGROUP BY StudentID, ProgrammeHAVING Min([Year]) = 2002[/code]Wed, 11 Jan 2012 13:54:19 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1233619-392-1.aspxI think it's actually even simpler.[code="sql"]SELECT StudentID, Programme, Min([Year]) AS [Year]FROM #StudentsGROUP BY StudentID, ProgrammeHAVING Min([Year]) = 2009[/code]You may not even need the HAVING clause depending on what you are looking for.ROW_NUMBER() is useful if you want to return fields that are neither used for the grouping nor for determining the Top/Bottom/First/Last record. That's not the situation here based solely on your sample data. You're grouping on two of the fields and using the remaining one for determining the first record, so you can use a simple GROUP BY.This has the added benefit that it only scans the table once instead of twice.DrewWed, 11 Jan 2012 13:33:39 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1233619-392-1.aspxHi Paul, thanks for your quick response. Must admit, your method is much simpler. (wish I had your SQL knowledge).The query is used as a filter - to remove all students who already registered the year before and now registered again for the same Programme. I apply the filter to another query.Wed, 11 Jan 2012 12:36:06 GMTkevin_nikolaihttp://www.sqlservercentral.com/Forums/Topic1233619-392-1.aspxIsn't it as simple as:[code="sql"]SELECT * FROM #Students AS sWHERE s.[Year] = 2009 AND NOT EXISTS ( SELECT * FROM #Students AS s2 WHERE s2.StudentID = s2.StudentID AND s2.Programme = s.Programme AND s2.[Year] = s.[Year] - 1 );[/code]Tue, 10 Jan 2012 17:33:55 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1233619-392-1.aspx/*Objective:if report is for Year 2009, and 2009 is the first registration year, then return Programme for student.if report is for Year 2010, and 2010 is the second registration year (based on same Programme as 2009), then dont return Programme for student. Same applies to 2011 & 2012 as for 2010.So report always checks for previous year/s, thereby evaluates if 1st or 2nd or 3rd year, etc.If 1st year, then there will only be one Programme for Student, so return results.If [b]NOT[/b] 1st year and same Programme for same Student, then return no results.If [b]NOT[/b] 1st year and different Programme for same Student, then return results.For testing with below code, replace 2009 with 2010,2011,2012 in WHERE [Year] = 2009*/Method1:--------create table #Students (StudentID INT,Programme varchar(20),[Year] INT);create table #Students2 (StudentID INT,Programme varchar(20),[Year] INT,[rank] INT); insert into #Studentsselect 61037,'PROGCS',2009union allselect 61037,'PROGCS',2010union allselect 61037,'PROGCS',2011union allselect 61037,'PROGCS',2012;insert into #Students2SELECT StudentID, Programme, [Year], rank = ( SELECT COUNT(*) FROM #Students t2 WHERE t2.StudentID = t1.StudentID AND t2.Programme = t1.Programme AND t2.[Year] <= t1.[Year] ) FROM #Students t1ORDER BY StudentID, Programme, [Year] SELECT [Year], Programme, StudentID--, rank FROM #Students2 WHERE [Year] = 2009AND RANK = (SELECT MIN(RANK) FROM #Students2)DROP TABLE #StudentsDROP TABLE #Students2--------------------------------------------------------------Method2:--------create table #Students (StudentID INT,Programme varchar(20),[Year] INT);insert into #Studentsselect 61037,'PROGCS',2009union ALLselect 61037,'PROGCS',2010union ALLselect 61037,'PROGCS',2011union ALLselect 61037,'PROGCS',2012;SELECT [Year], Programme, StudentID --, RowNumberFROM(SELECT [Year], Programme, StudentID,ROW_NUMBER() OVER (ORDER BY Programme) RowNumberFROM #Students) pWHERE [Year] = 2009 AND p.RowNumber = 1--WHERE p.RowNumber BETWEEN 1 AND 4DROP TABLE #Students--------------------------------------------------------------Does anyone else have a different approach ?Tue, 10 Jan 2012 15:58:39 GMTkevin_nikolai