Joining table to iteself to get most recent date as well as additional fields

• smrobin

  Ten Centuries

  Points: 1003

  More actions

  March 11, 2014 at 3:43 pm

  #289227

  I am querying a table log file in an attempt to get the most recent status of an item. The items can have a variety of different statuses: (A = Active, R = Repeat, L = liquidation......)

  I am only concerned about the most recent item status.

  Here is a sample of the data I am trying to report off of:

  CREATE TABLE [dbo].[item_status](

  [item_number] [varchar](20) NULL,

  [sku] [varchar](100) NULL,

  [Field_Name] [varchar](50) NULL,

  [Old_Value] [varchar](150) NULL,

  [New_Value] [varchar](150) NULL,

  [Change_Date] [smalldatetime] NULL

  ) ON [PRIMARY]

  insert item_status values ('33350','8X10','item_status','D','L','2014-02-03 22:07:00')

  insert item_status values ('33350','6X9','item_status','D','A','2014-02-03 22:07:00')

  insert item_status values ('33350','4X6','item_status','D','R','2014-02-03 22:07:00')

  insert item_status values ('33350','2X8','item_status','D','L','2014-02-03 22:07:00')

  insert item_status values ('33350','8X10','item_status','A','D','2013-12-03 22:06:00')

  insert item_status values ('33350','6X9','item_status','A','D','2013-12-03 22:06:00')

  insert item_status values ('33350','4X6','item_status','A','D','2013-12-03 22:06:00')

  insert item_status values ('33350','2X8','item_status','A','D','2013-12-03 22:06:00')

  I am trying to make it so the data comes out in the following format:

  item_numbersku Field_Name Old_Value New_Value Change_Date

  33350 8X10 item_status D L 2014-02-03 22:07:00

  33350 6X9 item_status D A 2014-02-03 22:07:00

  33350 4X6 item_status D R 2014-02-03 22:07:00

  33350 2X8 item_status D L 2014-02-03 22:07:00

  Any thoughts on how to do this? I have tried join to the same table - but I am still unable to get it to work.

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  March 11, 2014 at 3:57 pm

  #1696166

  One possible solution using windowing functions:

  SELECT item_number,sku,Field_Name,Old_Value,New_Value,Change_Date

  FROM

  (

  SELECT item_number,sku,Field_Name,Old_Value,New_Value,Change_Date

  ,RID = ROW_NUMBER() OVER (PARTITION BY sku ORDER BY Change_Date DESC)

  FROM [dbo].[item_status]

  ) tmp

  WHERE RID = 1;

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP