asked
T-SQL get record from the last value of a column
Hi, sample table TID HistoryID Action Date desc === ========= ====== ========= ==== 1 1 A 1/11/2013 abc 1 2 B 2/11/2013 xyz 2 1 A 2/11/2013 abc 2 2 C 3/11/2013 xyz 3 1 A 4/11/2013 abc 3 2 B 5/11/2013 xyz I want to find all the rows for a particular TID where the Action="B" is the last row For example, output should be: 1 1 A 1/11/2013 abc 1 2 B 2/11/2013 xyz 3 1 A 4/11/2013 abc 3 2 B 5/11/2013 xyz How can that be done? Regards, Mandar
declare @yourtable table (TID int, HistoryId int, [action] char(1), [Date] date, [desc] varchar(10)); insert into @yourtable select 1,1,'A','1 nov 2013','abc'; insert into @yourtable select 1,2,'B','2 nov 2013','xyz'; insert into @yourtable select 2,1,'A','2 nov 2013','abc'; insert into @yourtable select 2,2,'C','3 nov 2013','xyz'; insert into @yourtable select 3,1,'A','4 nov 2013','abc'; insert into @yourtable select 3,2,'B','5 nov 2013','xyz'; with cte as ( select row_number()over(partition by TID order by HistoryId desc) as rn, TID , HistoryId , action , Date , [desc] from @yourtable ) select * from @yourtable where TID in (select TID from cte where rn = 1 and [action] = 'B' --the TIDs where B is the last action )
15 People are following this question.
