Get the most recent row - how to

• dubem1-878067

  Ten Centuries

  Points: 1044

  More actions

  September 28, 2012 at 2:08 pm

  #268323

  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?

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  September 28, 2012 at 2:15 pm

  #1543624

  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/

• Roland Alexander STL

  SSCrazy

  Points: 2915

  More actions

  September 28, 2012 at 2:16 pm

  #1543625

  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