SQLServerCentral.com / SQL Server 2008 - General / SQL Server 2008 / Return Empty is a specific record is encontered. / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 17:41:01 GMT20RE: Return Empty is a specific record is encontered.http://www.sqlservercentral.com/Forums/Topic1470507-391-1.aspx[quote][b]Erland Sommarskog (7/4/2013)[/b][hr]This is easily achieved with CTE that numbers the rows and then you can filter on rowno = 1:[code="sql"]; WITH numbering AS ( SELECT RecID, DateEntered, Status, row_number() OVER(PARTITION BY RecID ORDER BY DateEntered DESC) AS rowno FROM #Temp1)SELECT RecID, DateEntered, StatusFROM numberingWHERE rowno = 1 AND Status &lt;&gt; 'C' [/code][/quote]Mr. Sommarskog,Excuse my impetuous presumtiveness, but I just wanted to say I've read a number of your articles and learned a lot from them. I noticed you posting here just yesterday in fact and I just had to say something, but most especially thanks for your contribution to the SQL body of knowledge.DwainThu, 04 Jul 2013 18:50:13 GMTdwain.cRE: Return Empty is a specific record is encontered.http://www.sqlservercentral.com/Forums/Topic1470507-391-1.aspxThis is easily achieved with CTE that numbers the rows and then you can filter on rowno = 1:[code="sql"]; WITH numbering AS ( SELECT RecID, DateEntered, Status, row_number() OVER(PARTITION BY RecID ORDER BY DateEntered DESC) AS rowno FROM #Temp1)SELECT RecID, DateEntered, StatusFROM numberingWHERE rowno = 1 AND Status &lt;&gt; 'C' [/code]Thu, 04 Jul 2013 14:25:43 GMTErland SommarskogReturn Empty is a specific record is encontered.http://www.sqlservercentral.com/Forums/Topic1470507-391-1.aspxHi all,This seems to be very easy (and probably is!!) but I'm just stuck here.I have a table that contains an ID, a date and a Status.I want to return the top row (most recent datetime) if any of the status for the same Id is not C (as in Canceled).Here's an example:create table #Temp1(RecID int, DateEntered Datetime, Status Varchar(1))INSERT INTO #Temp1 VALUES (1,'01-01-2013 10:20:10', 'N')INSERT INTO #Temp1 VALUES (1,'01-02-2013 08:14:00', 'R')INSERT INTO #Temp1 VALUES (1,'01-03-2013 03:30:00', 'E')INSERT INTO #Temp1 VALUES (2,'01-01-2013 14:58:00', 'N')INSERT INTO #Temp1 VALUES (2,'01-02-2013 08:23:00', 'R')INSERT INTO #Temp1 VALUES (2,'01-04-2013 22:14:00', 'C')select * from #Temp1drop table #temp1In this examples my result set should be:1 ; 01-03-2013 03:30:00 ; ENo record should be returned from RecId 2 because the last line is a 'C'.The 'C' status will always be in the last row.Thank you all for your help!!Thu, 04 Jul 2013 14:18:08 GMTFastRider30