Get the most recent row - how to

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Pretty sparse on details but I think something like this is what you want.

;with Data as

(

select 1 as ID, 'A' as Status, 1 as Sequence union all

select 1, 'C', 2 union all

select 1, 'B', 3 union all

select 2, 'B', 1 union all

select 3, 'C', 1 union all

select 3, 'A', 2

)

, DataGrouped as

(

select ROW_NUMBER() over (partition by ID order by Sequence desc) as RowNum,* from data

)

select ID, Status

from DataGrouped where RowNum = 1

Notice how I posted sample data in a consumable format? This is something you should do on future posts.

Also, if this is your whole table structure I would strongly advise you to come up with something for a primary key. Hope this helps.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 2

An alternate:

create table #tmp (status_id int, status_code char(1), status_sequence int);

insert into #tmp

values

(1, 'A', 1),

(1, 'B', 3),

(2, 'B', 1),

(3, 'C', 1),

(3, 'A', 2);

select

status_id,

status_code

from

#tmp t1

where

status_sequence = (select max(status_sequence) from #tmp t2 where status_id = t1.status_id);

drop table #tmp;

Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer