question

Mandar Alawani avatar image
Mandar Alawani 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
t-sqlcteorder-bypartition
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you make your example a little easier to read? What does your query look like now? Can you share that? The more you provide the better we can help.
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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 )
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.