September 28, 2012 at 2:08 pm
I have a table where all the status for a same entity is saved
ID Status Sequence
-- ------- -----------
1 A 1
1 C 2
1 B 3
2 B 1
3 C 1
3 A 2
ID represent a foreign key to something
I need a query to get the most recent status based on the sequence, the higher is the sequence, the more recent is the status
Result :
1 B
2 B
3 A
How to?
September 28, 2012 at 2:15 pm
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.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 28, 2012 at 2:16 pm
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy